Sqlserver给表名和列名添加注释

一、表注释

    1、添加

--固定写法
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description',@level1type=N'TABLE',
@level0type=N'SCHEMA',@level0name=N'dbo', 
--自定义
@value=N'注释内容' , @level1name=N'表名'

--例
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description',@level1type=N'TABLE',
@level0type=N'SCHEMA',@level0name=N'dbo', 
@value=N'用户表' , @level1name=N'sys_user'

     2、查看

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

二、列名注释

    1、添加

--固定写法
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'注释内容'

--例
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description',  
@level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level2type=N'COLUMN',
@level1name=N'sys_user', @level2name=N'username',@value=N'用户名称'

    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'