Friday 1 May 2015

Getting Column Descriptions of a Table in MS SQL

--Run in the Context of the Database where the table resides...

DECLARE @Schema VARCHAR(50)
DECLARE @Table VARCHAR(50)

SET @Schema = 'Your Schema Here'
SET @Table = 'Your Table Here'


SELECT sch.name AS [Schema],
        st.name AS [Table],
        sept.value AS [TableDescription],
        sc.name AS [Column],
        sep.value AS [ColumnDescription],
        ty.name AS [DataType],
        sc.max_length AS [DataLength],
        sc.precision AS [DatePrecision],
        sc.scale AS [DateScale],
        sc.collation_name AS [ColumnCollation]
FROM sys.tables st
INNER JOIN sys.schemas sch ON st.schema_id = sch.schema_id
LEFT JOIN sys.extended_properties sept ON st.object_id = sept.major_id
                                        AND st.parent_object_id = sept.minor_id
                                        AND sept.name = 'MS_Description'
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
LEFT JOIN sys.extended_properties sep ON st.object_id = sep.major_id
                                        AND sc.column_id = sep.minor_id
                                        AND sep.name = 'MS_Description'
INNER JOIN sys.types ty ON sc.user_type_id = ty.user_type_id
WHERE st.name = @Table
AND sch.name = @Schema
;