CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Renato
Renato

Reputation: 556

Alternative to sys.columns.max_length

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

Upvotes: 8

Views: 5999

Answers (1)

Alex
Alex

Reputation: 5157

Have you tried:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Upvotes: 8

Related Questions