# 描述性注释 MS_Description

描述性注释 MS_Description

查看特别表，所有字段 描述性注释

SELECT 
    obj.name AS ObjectName, 
    col.name AS ColumnName, 
    ep.value AS Description
FROM 
    sys.extended_properties ep
INNER JOIN 
    sys.objects obj ON ep.major_id = obj.object_id
LEFT JOIN 
    sys.columns col ON ep.major_id = col.object_id AND ep.minor_id = col.column_id
WHERE 
    ep.name = N'MS_Description' -- 查找名称为 'MS_Description' 的属性
    AND obj.name = N'sqb_file'  -- 指定表名

ObjectName                     ColumnName                     Description                   
------------------------------ ------------------------------ ------------------------------
sqb_file                       sqb_s11                        (长度,宽度,厚度)的尺寸标准               
sqb_file                       sqb_del                        删除标志位                         

查看特别表，特定字段 描述性注释

SELECT 
    obj.name AS ObjectName, 
    col.name AS ColumnName, 
    ep.value AS Description
FROM 
    sys.extended_properties ep
INNER JOIN 
    sys.objects obj ON ep.major_id = obj.object_id
LEFT JOIN 
    sys.columns col ON ep.major_id = col.object_id AND ep.minor_id = col.column_id
WHERE 
    ep.name = N'MS_Description' -- 查找名称为 'MS_Description' 的属性
    AND obj.name = N'sqb_file'  -- 指定表名
    AND col.name = N'sqb_del';  -- 指定列名

ObjectName                     ColumnName                     Description                   
------------------------------ ------------------------------ ------------------------------
sqb_file                       sqb_del                        删除标志位                         

新增描述性注释

-- 为新添加的字段添加描述性注释
EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'删除标志位',
    @level0type = N'Schema', @level0name = N'dbo', 
    @level1type = N'Table',  @level1name = N'sqb_file',
    @level2type = N'Column', @level2name = N'sqb_del';

修改描述性注释

-- 修改 sqb_file 表中 sqb_del 列的描述性注释
EXEC sp_updateextendedproperty 
    @name = N'MS_Description', 
    @value = N'这是要修改删除标志位',
    @level0type = N'Schema', @level0name = N'dbo', 
    @level1type = N'Table',  @level1name = N'sqb_file',
    @level2type = N'Column', @level2name = N'sqb_del';