sql server 添加表注释、字段注释

来源：互联网发布：sql可以装在d盘吗编辑：程序博客网时间：2024/05/02 02:38

--为字段添加注释 --格式如右：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;-- 说明：-- 1.增加、修改、删除注释调用不同的存储过程  -- 2.增加、修改注释调用的存储过程参数数量和含义相同，删除备注比前两者少了一个“备注内容”的参数-- 3.为表添加注释相比于为字段添加注释，最后两个参数为null

--查看表的所有字段注释use HealthCare;SELECT [ColumnName] = [Columns].name ,        [Description] = [Properties].value,        [SystemTypeName] = [Types].name ,        [Precision] = [Columns].precision ,        [Scale] = [Columns].scale ,        [MaxLength] = [Columns].max_length ,        [IsNullable] = [Columns].is_nullable ,        [IsRowGUIDCol] = [Columns].is_rowguidcol ,        [IsIdentity] = [Columns].is_identity ,        [IsComputed] = [Columns].is_computed ,        [IsXmlDocument] = [Columns].is_xml_document FROM    sys.tables AS [Tables]        INNER JOIN sys.columns AS [Columns] ON [Tables].object_id = [Columns].object_id        INNER JOIN sys.types AS [Types] ON [Columns].system_type_id = [Types].system_type_id                                           AND is_user_defined = 0                                           AND [Types].name <> 'sysname'        LEFT OUTER JOIN sys.extended_properties AS [Properties] ON [Properties].major_id = [Tables].object_id                                                              AND [Properties].minor_id = [Columns].column_id                                                              AND [Properties].name = 'MS_Description'WHERE   [Tables].name ='表名' -- and [Columns].name = '字段名'ORDER BY [Columns].column_id

 --查看表的注释use HealthCare; select isnull(value,'') from sys.extended_properties ex_p where ex_p.minor_id=0and ex_p.major_id in (select id from sys.sysobjects a where a.name='表名')

0 0