sqlserver 获取表和字段的注释方法

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\n" +
"\n" +
" [tableName] = i_s.TABLE_NAME,\n" +
"\n" +
" [columnName] = i_s.COLUMN_NAME,\n" +
"\n" +
" [description] = s.value\n" +
"\n" +
"FROM\n" +
"\n" +
" INFORMATION_SCHEMA.COLUMNS i_s\n" +
"\n" +
"LEFT OUTER JOIN\n" +
"\n" +
" sys.extended_properties s\n" +
"\n" +
"ON\n" +
"\n" +
" s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)\n" +
"\n" +
" AND s.minor_id = i_s.ORDINAL_POSITION\n" +
"\n" +
" AND s.name = 'MS_Description'\n" +
"\n" +
"WHERE\n" +
"\n" +
" OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0\n" +
"\n" +
"ORDER BY\n" +
"\n" +
" i_s.TABLE_NAME, i_s.ORDINAL_POSITION";