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;