SELECT
A.name AS table_name,
B.name AS column_name,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
SELECT DISTINCT
d.name,
f.value
FROM
syscolumns a
LEFT JOIN systypes b ON a.xusertype= b.xusertype
INNER JOIN sysobjects d ON a.id= d.id
AND d.xtype= 'U'
AND d.name<> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault= e.id
LEFT JOIN sys.extended_properties g ON a.id= G.major_id
AND a.colid= g.minor_id
LEFT JOIN sys.extended_properties f ON d.id= f.major_id
AND f.minor_id= 0 ;
String sql = "SELECT
" +
"
" +
" [tableName] = i_s.TABLE_NAME,
" +
"
" +
" [columnName] = i_s.COLUMN_NAME,
" +
"
" +
" [description] = s.value
" +
"
" +
"FROM
" +
"
" +
" INFORMATION_SCHEMA.COLUMNS i_s
" +
"
" +
"LEFT OUTER JOIN
" +
"
" +
" sys.extended_properties s
" +
"
" +
"ON
" +
"
" +
" s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
" +
"
" +
" AND s.minor_id = i_s.ORDINAL_POSITION
" +
"
" +
" AND s.name = 'MS_Description'
" +
"
" +
"WHERE
" +
"
" +
" OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
" +
"
" +
"ORDER BY
" +
"
" +
" i_s.TABLE_NAME, i_s.ORDINAL_POSITION";