I have written the following script to get some data on the columns of a specified table:
DECLARE @QueryTable varchar(35);
SET @QueryTable = 'Test';
SELECT DISTINCT
sys.columns.name AS 'Column',
sys.types.name AS 'Data type',
CASE WHEN sys.types.name IN ('varchar', 'char') THEN CAST(sys.columns.max_length AS varchar(5))
WHEN sys.types.name IN ('decimal', 'numeric') THEN CAST(CAST (sys.columns.precision AS nvarchar(10)) + ', ' + CAST(sys.columns.scale AS nvarchar(10)) AS nvarchar(10))
WHEN sys.types.name IN ('nvarchar', 'nchar') THEN CAST(sys.columns.max_length / 2 AS varchar(5))
ELSE '-' END AS 'Max size',
CASE WHEN sys.columns.is_nullable = 1 THEN 'YES'
WHEN sys.columns.is_nullable = 0 THEN 'NO' END AS 'Allow nulls',
CASE WHEN sys.columns.name IN (SELECT Col.COLUMN_NAME from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.CONSTRAINT_NAME
AND Col.Table_Name = Tab.TABLE_NAME
AND Constraint_Type = 'PRIMARY KEY'
AND Col.Table_Name = @QueryTable) THEN 'PK'
ELSE '' END AS 'Primary Key'
FROM
sys.columns, sys.types, sys.tables
WHERE
sys.tables.object_id = sys.columns.object_id AND
sys.types.system_type_id = sys.columns.system_type_id AND
sys.types.user_type_id = sys.columns.user_type_id AND
sys.tables.name = @QueryTable
ORDER BY sys.columns.name
Naturally, for nvarchar and nchar types I get a max_length which is twice the size of the actual maximum character length for that field. My question is, is there anywhere I could directly obtain that actual maximum character length defined when the column was created, without resorting to this indirect calculation?
The approach I have used for outputting data for numeric/decimal types is also kind of a mess in my opinion.
Thank you
The max_length field above from the sys. columns view is maximum length the field value can have. It's NOT the maximum length of field's data from MyTable. What the OP wanted was to compare the length of the data type to the maximum actual length of data in the field.
Use the built-in functions for length and max on the description column: SELECT MAX(LEN(DESC)) FROM table_name; Note that if your table is very large, there can be performance issues.
sys.columns.system_type_id = sys.types.user_type_idFor a built-in type, it returns the built-in type. For a user-defined type, it returns the built-in base type. This might make sense, for example, if you want to get all varchar columns, including all user-defined columns based on varchar.
sys. columns is a system table and is used for maintaining information on columns in a database. For every column added in a database, a record is created in the sys. columns table.
Have you tried:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With