SQL SERVER添加表注释、字段注释

--取表字段注释
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;