【SQL Server】表盒字段的注释

之前写过使用SQL Server查看表以及字段的注释 。并没有修改和删除字段注释。现在将其补充完整。

 1 -- 查询所有表格注释
 2 SELECT A.name ,C.value
 3 FROM sys.tables A
 4     LEFT JOIN sys.extended_properties C ON C.major_id = A.object_id
 5 WHERE C.minor_id=0 AND a.name = 'sys_user'
 6 group by A.name ,C.value
 7 
 8 -- 添加表注释
 9 execute sp_addextendedproperty 'MS_Description','用户信息表','user','dbo','table','sys_user'
10  
11 -- 修改表注释
12 execute sp_updateextendedproperty 'MS_Description','用户信息表','user','dbo','table','sys_user'
13  
14 -- 删除表注释
15 execute sp_dropextendedproperty 'MS_Description','user','dbo','table','sys_user'
16 
17 
18 -- 查询字段注释
19 SELECT A.name AS table_name,B.name AS column_name,C.value AS column_description
20 FROM sys.tables A
21     INNER JOIN sys.columns B ON B.object_id = A.object_id
22     LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
23 WHERE A.name = 'sys_user' -- 表名
24 
25 
26  -- 字段加注释
27 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_user', @level2type=N'COLUMN',@level2name=N'user_id'
28 
29  -- 字段加注释
30 --EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注释内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'字段名'
31 
32  
33 --修改字段注释
34 EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'用户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_user', @level2type=N'COLUMN',@level2name=N'user_id'
35 
36 --删除字段注释
37 execute sp_dropextendedproperty 'MS_Description','user','dbo','table','sys_user','column','user_id'