sql server2008给数据表,字段,添加修改注释 柔光的暖阳◎ 2021-06-26 16:06 580阅读 0赞 -- 表加注释 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注释内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名' --例如: EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系统设置表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CM01_SYSTEM' --新增数据字段注释 EXECUTE sp_addextendedproperty 'MS_Description', '收益浮动表', 'user', 'dbo', 'table', 'COM_IncomeFloat', 'column', 'RowId'; --修改数据字段注释 EXECUTE sp_updateextendedproperty 'MS_Description', '收益浮动表', 'user', 'dbo', 'table', 'COM_IncomeFloat', 'column', 'RowId'; * `MS_Description` :调用的`Proc` * `COM_IncomeFloat` :表名 * `RowId` :表字段 ## 查询字段注释 ## 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 = 'COM_IncomeFloat' -------------------- ## sql server 添加表注释、字段注释 ## --为字段添加注释 --格式如右: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 --查看表的注释 select isnull(value,'') from sys.extended_properties ex_p where ex_p.minor_id=0 and ex_p.major_id in (select id from sys.sysobjects a where a.name='表名') --查看表的所有字段注释 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 -------------------- ## SqlServer如何给表添加新的字段以及字段注释 ## **语法:** USE 数据库名ALTER TABLE 表名 ADD 字段名 类型 默认值 是否为空;EXEC Sp_addextendedproperty N'MS_Description', N'注释内容', N'SCHEMA', N'dbo', N'TABLE', N'表名', N'COLUMN', N'字段名'; **事例:** USE [gameagent] ALTER TABLE [gameagent].[dbo].[charge_consume] ADD card_consume INT DEFAULT(0) NOT NULL; EXEC Sp_addextendedproperty N'MS_Description', N'房卡消耗', N'SCHEMA', N'dbo', N'TABLE', N'charge_consume', N'COLUMN', N'card_consume'; **注意:** 建议设置值不为空,且要给默认值; 新加的字段在表中的位置似乎没法自定义。
还没有评论,来说两句吧...