【SQL Server】表和字段的注释

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

-- 查询所有表格注释
SELECT A.name ,C.value
FROM sys.tables A
    LEFT JOIN sys.extended_properties C ON C.major_id = A.object_id
WHERE C.minor_id=0 AND a.name = 'sys_user'
group by A.name ,C.value

-- 添加表注释
execute sp_addextendedproperty 'MS_Description','用户信息表','user','dbo','table','sys_user'
 
-- 修改表注释
execute sp_updateextendedproperty 'MS_Description','用户信息表','user','dbo','table','sys_user'
 
-- 删除表注释
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','sys_user'


-- 查询字段注释
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 = 'sys_user' -- 表名


 -- 字段加注释
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'

 -- 字段加注释
--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'字段名'

 
--修改字段注释
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'

--删除字段注释
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','sys_user','column','user_id'