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)…
DECLARE @TableName VARCHAR(128)
SET @TableName = ‘Catalogue’
SELECT
T.table_name AS TableName,
C.ordinal_position AS ColumnOrder,
C.[column_name] AS ColumnName,
ISNULL(P.value, ”) AS ColumnDescription,
C.DATA_TYPE AS DataType,
COALESCE(numeric_precision,character_maximum_length,0) AS Length,
COALESCE(numeric_scale,0) AS DecimalPos,
C.is_nullable AS IsNullable,
ISNULL(C.COLUMN_DEFAULT, ”) AS DefaultValue
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