Data Dictionary from within SQL Server

I knew you could do this, but never took the time to find out exactly which sys… table the comments got stored in.  sysProperties is what you want, and this article on SQLServerCentral tells you how…

The easiest way to get them ‘in’ is in Enterprise Manager, and someone posted a slightly amended version of the code to get them out (I also added IsNullable)…


SET @TableName ‘Catalogue’

T.table_name AS TableName,
AS ColumnOrder,
AS ColumnName,
ISNULL(P.value, AS ColumnDescription,
AS DataType,
COALESCE(numeric_precision,character_maximum_length,0AS Length,
COALESCE(numeric_scale,0AS DecimalPos,
AS IsNullable,
FROM information_schema.tables T 
INNER JOIN information_schema.columns C 
ON T.table_name C.table_name 
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N’MS_Description’N’user’,N’dbo’,N’table’, @TableName, N’column’null) P 
ON C.column_name P.objname
WHERE T.table_name @TableName

Colorized by: CarlosAg.CodeColorizer