Column Properties

There are different ways to query the meta data in SQL Server. The system catalog views in SQL Server are one great improvement. Here is one example on retrieving properties for all columns, including default values and description (if defined as extended property).

SELECT SCHEMA_NAME(T.schema_id) AS 'Schema',

       T.name AS 'Table Name',

       C.name AS 'Column Name',

       S.name AS 'Data Type',

       C.precision AS 'Precision',

       C.scale AS 'Scale',

       CASE WHEN S.name IN ('nvarchar', 'nchar')

            THEN C.max_length/2

            ELSE C.max_length

       END AS 'Length',

       CASE WHEN C.is_nullable = 1

            THEN 'Y'

            ELSE 'N'

       END AS 'Allow NULLs',

       D.definition AS 'Default Value',

       P.value AS 'Description'

FROM sys.tables AS T

JOIN sys.columns AS C

  ON T.object_id = C.object_id

JOIN sys.types AS S

  ON C.user_type_id = S.user_type_id

LEFT OUTER JOIN sys.default_constraints AS D

  ON C.object_id = D.parent_object_id

 AND C.column_id = D.parent_column_id

LEFT OUTER JOIN sys.extended_properties  AS P

  ON C.object_id = P.major_id

 AND C.column_id = P.minor_id

 AND P.class = 1

 AND P.name = 'MS_Description'

WHERE T.type = 'U'

ORDER BY T.name, C.column_id;

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *