Getting the Description Column from SQL Server

If you are storing valuable information such as comments, etc in the Description for a given column in your table you may want to export it to excel or in general just get to it via t-sql. It is actually pretty simple to get it. Below is a query you can run from the database you want to report on. I have exluded views, non-user tables, default tables, and system diagram tables, but you can change that by commenting out the appropriate statements in the where clause.

SELECT
[Schema] = ColumnInfo.TABLE_SCHEMA,
[Table Name] = ColumnInfo.TABLE_NAME,
[Column Name] = ColumnInfo.COLUMN_NAME,
[Position In Table] = ColumnInfo.ORDINAL_POSITION,
[Date Type] = ColumnInfo.[Data_Type],
[Description] = Properties.value
FROM
INFORMATION_SCHEMA.COLUMNS ColumnInfo
LEFT OUTER JOIN sys.extended_properties Properties
ON
Properties.major_id = OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME)
AND Properties.minor_id = ColumnInfo.ORDINAL_POSITION
AND Properties.name = 'MS_Description'
WHERE
-- exclude ones created when SQL Server was installed
OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsMSShipped')=0
 
-- only get tables (no views)
and OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsTable')=1
 
-- exclude ones created when SQL Server was installed
and OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsUserTable')=1
 
-- exclude tables used for system diagrams
and ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME <> 'dbo.sysdiagrams'

--AND ColumnInfo.TABLE_NAME = 'table_name'
ORDER BY
ColumnInfo.TABLE_NAME, ColumnInfo.ORDINAL_POSITION

I got the basics for this from this post.