How to select column comments in SQL Server?

Question

I need to select the column comments of a table. What table has this information?

Answer 1

There is no SQL standard compliant support for comments in SQL Server.

The only thing that comes close to the usual comment on ... syntax is using an extended property to store the comments.

To list the values of an extended property, you can use the following statement:

SELECT objname, cast(value as varchar) as value 
FROM fn_listextendedproperty ('MS_DESCRIPTION','schema', 'dbo', 'table', 'your_table', 'column', null);

This assumes you are using property named MS_DESCRIPTION to store the comments.

To set a comment using an extended property you have to use the sp_addextendedproperty() function.

Answer 2

SELECT
   *
FROM
   sys.extended_properties
WHERE
   major_id = OBJECT_ID('mytable')
   AND
   minor_id = COLUMNPROPERTY(major_id, 'MyColumn', 'ColumnId')