--取表字段注释
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
WHERE A.name = 't_dept';--取视图注释SELECT A.name AS table_name, B.name AS column_name, C.value AS column_description FROM sys.views 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 = 'v_name';select * from sys.extended_properties
where major_id=66815300
--取表注释
select f.value,d.name from sysobjects d
left outer join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
where xtype='U'
--and D.name like 'MPS%'
and f.value is NOT null
order by d.name
--为字段添加注释
--Eg. execute sp_addextendedproperty 'MS_Description','字段备注信息','user','dbo','table','字段所属的表名','column','添加注释的字段名';
execute sp_addextendedproperty 'MS_Description','add by liyc. 诊断类别码','user','dbo','table','DiagRecord','column','DiagTypeCode';
--修改字段注释
execute sp_updateextendedproperty 'MS_Description','add by liyc.','user','dbo','table','DiagRecord','column','DiagTypeCode';
--删除字段注释
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','DiagRecord','column','DiagTypeCode';
-- 添加表注释
execute sp_addextendedproperty 'MS_Description','诊断记录文件','user','dbo','table','DiagRecord',null,null;
-- 修改表注释
execute sp_updateextendedproperty 'MS_Description','诊断记录文件1','user','dbo','table','DiagRecord',null,null;
-- 删除表注释
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','DiagRecord',null,null;
