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;
Labels: t-sql programming