-
表注释
EXEC sys.sp_addextendedproperty @name=N'MS_Description',@level1type=N'TABLE', @level0type=N'SCHEMA',@level0name=N'dbo', @value=N'注释内容' , @level1name=N'表名'
eg:给T_COLR添加注释
EXEC sys.sp_addextendedproperty @name=N'MS_Description',@level1type=N'TABLE', @level0type=N'SCHEMA',@level0name=N'dbo', @value=N'这是一些注释' , @level1name=N'T_COLR'
也可以写成
EXEC sp_addextendedproperty N'MS_Description', '这是一些注释', N'SCHEMA', dbo,N'TABLE', T_COLR, null, null;
-
列注释
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level2type=N'COLUMN', @level1name=N'表名', @level2name=N'列名',@value=N'注释内容'
eg:给T_COLR的Color_Name添加注释
EXEC sys.sp_addextendedproperty @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level2type=N'COLUMN', @level1name=N'T_COLR', @level2name=N'Color_Name',@value=N'这是一些注释'
也可以写成
EXEC sp_addextendedproperty N'MS_Description', '这是一些注释', N'SCHEMA', dbo,N'TABLE', T_COLR, N'column', Color_Name;
-
查询注释
(1) 查询表注释SELECT st.name tablename, sep.value tabledesc FROM sys.tables st JOIN sys.extended_properties sep ON sep.major_id = st.object_id AND minor_id = 0
(2) 查询列注释
SELECT sc.name colname, sep.value coldesc FROM sys.tables st JOIN sys.columns sc ON sc.object_id = st.object_id LEFT JOIN sys.extended_properties sep ON sep.major_id = sc.object_id AND sep.minor_id = sc.column_id WHERE st.name = N'sys_user'
-
更新注释
使用 sp_updateextendedproperty 更新注释,参数与添加注释一致
【SQLSERVER】给表、列添加注释
于 2023-07-22 09:01:20 首次发布