Geek Answers Handbook

Alternative sys.columns.max_length

I wrote the following script to get some data about the columns of the 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 the nvarchar and nchar types, I get the maximum length, which is two times the actual maximum character length for this field. My question is, is there anywhere that I could directly get this actual maximum character length determined when creating the column without resorting to this indirect calculation?

The approach I used to output data for numeric / decimal types is also, in my opinion, a mess.

thank

+4
source share
1 answer

You tried:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
+3

More articles:


All Articles