sqlserver的表和字段的描述

mysql的字段和表的描述无须多说，因为就在create的语句里，查ddl就行。

sqlserver的字段描述和表描述，麻烦一点，在另一表里：

查字段描述：

SELECT
A.name AS 表名,
B.name AS 字段名,
C.value AS 字段说明
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
WHERE A.name = '表名'

SELECT CAST(T.NAME AS VARCHAR)   AS '表名',
               CAST(T1.VALUE AS VARCHAR) AS '表描述'
    FROM SYS.TABLES T
                 LEFT JOIN SYS.EXTENDED_PROPERTIES T1 ON T.OBJECT_ID = T1.MAJOR_ID
            AND T1.MINOR_ID = 0
    where T.name='表名'
    ORDER BY T.NAME

这两信息的维护，可以用以下SQL:

-- 添加表注释
execute sp_addextendedproperty 'MS_Description', '表备注内容', 'SCHEMA', 'dbo','table', '表名',NULL,NULL;
-- 修改表注释
execute sp_updateextendedproperty 'MS_Description','表备注内容','user','dbo','table','表名',NULL,NULL;
-- 添加字段注释
execute sp_addextendedproperty 'MS_Description','字段备注内容','user','dbo','table','字段表名','column','字段名';
-- 修改字段注释
execute sp_updateextendedproperty 'MS_Description','字段备注内容','user','dbo','table','字段表名','column','字段名';
-- 添加表名注释相当于添加字段，所以后面二个为NULL