SqlServer创建数据表描述及列描述信息

Intro

Q: 为什么要创建描述信息？

A: 鼠标悬停在对应表和列上时，会出现描述信息，可以提高工作的效率，借助工具我们根据数据表可以生成Model，可以将描述信息。

ms_desc_table ms_desc_column

添加描述语法

添加描述存储过程【sp_addextendedproperty】语法

    sp_addextendedproperty  
        [ @name = ] { 'property_name' }  
        [ , [ @value = ] { 'value' }   
            [ , [ @level0type = ] { 'level0_object_type' }   
            , [ @level0name = ] { 'level0_object_name' }   
                    [ , [ @level1type = ] { 'level1_object_type' }   
                    , [ @level1name = ] { 'level1_object_name' }   
                            [ , [ @level2type = ] { 'level2_object_type' }   
                            , [ @level2name = ] { 'level2_object_name' }   
                            ]   
                    ]  
            ]   
        ]   
    [;] 

参数说明

[ @name ] = { 'property_name' }
Is the name of the property to be added. property_name is sysname and cannot be NULL. Names can also include blank or non-alphanumeric character strings, and binary values.

[ @value= ] { 'value'}
Is the value to be associated with the property. value is sql_variant, with a default of NULL. The size of value cannot be more than 7,500 bytes.

[ @level0type= ] { 'level0_object_type' }
Is the type of level 0 object. level0_object_type is varchar(128), with a default of NULL.
Valid inputs are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, PLAN GUIDE, and NULL.

[ @level0name= ] { 'level0_object_name' }
Is the name of the level 0 object type specified. level0_object_name is sysname with a default of NULL.

[ @level1type= ] { 'level1_object_type' }
Is the type of level 1 object. level1_object_type is varchar(128), with a default of NULL. Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.

[ @level1name= ] { 'level1_object_name' }
Is the name of the level 1 object type specified. level1_object_name is sysname, with a default of NULL.

[ @level2type= ] { 'level2_object_type' }
Is the type of level 2 object. level2_object_type is varchar(128), with a default of NULL. Valid inputs are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.

[ @level2name= ] { 'level2_object_name' }
Is the name of the level 2 object type specified. level2_object_name is sysname, with a default of NULL.

[ @name = ] { 'property_name' }

要添加的属性名称。property_name 的数据类型为 sysname，它不能是 NULL。名称可能还包括空白或非字母数字字符串和二进制值。

[ @value = ] { 'value' }

将要与属性相关联的值。value 的数据类型为 sql_variant，带有默认设置 NULL。value 的大小不能超过 7,500 字节；否则 SQL Server 会产生错误。

[ @level0type = ] { 'level0_object_type' }

用户或用户定义类型。level0_object_type 的数据类型为 varchar(128)，其默认值为 NULL。有效的输入是 ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, PLAN GUIDE 和 NULL

[ @level0name = ] { 'level0_object_name' }

指定的 0 级对象类型的名称。level0_object_name 的数据类型为 sysname，其默认值为 NULL。

[ @level1type = ] { 'level1_object_type' }

1 级对象的类型。level1_object_type 的数据类型为 varchar(128)，其默认值为 NULL。有效的输入是 AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION 和 NULL。

[ @level1name = ] { 'level1_object_name' }

指定的 1 级对象类型的名称。level1_object_name 的数据类型为 sysname，其默认值为 NULL。

[ @level2type = ] { 'level2_object_type' }

2 级对象的类型。level2_object_type 的数据类型为 varchar(128)，其默认值为 NULL。有效的输入是 COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER 和 NULL。

[ @level2name = ] { 'level2_object_name' }

指定的 2 级对象类型的名称。level2_object_name 的数据类型为 sysname,其默认值为 NULL。

返回值为0则成功，1则失败

更新描述

sp_updateextendedproperty  
    [ @name = ]{ 'property_name' }   
    [ , [ @value = ]{ 'value' }  
        [, [ @level0type = ]{ 'level0_object_type' }  
         , [ @level0name = ]{ 'level0_object_name' }  
              [, [ @level1type = ]{ 'level1_object_type' }  
               , [ @level1name = ]{ 'level1_object_name' }  
                     [, [ @level2type = ]{ 'level2_object_type' }  
                      , [ @level2name = ]{ 'level2_object_name' }  
                     ]  
              ]  
        ]  
    ]  

更新描述 和 添加的语法差不多，在此就不再赘述

USER 还是 SCHEMA

msdn 给出的文档里这么说：

The ability to specify USER as a level 0 type in an extended property of a level 1 type object will be removed in a future version of SQL Server. Use SCHEMA as the level 0 type instead. For example, when defining an extended property on a table, specify the schema of the table instead of a user name. The ability to specify TYPE as level-0 type will be removed in a future version of SQL Server. For TYPE, use SCHEMA as the level 0 type and TYPE as the level 1 type.

在级别1类型对象的扩展属性中将USER指定为级别0类型的功能将在未来版本的SQL Server中删除。
使用SCHEMA作为0级类型。 例如，在表上定义扩展属性时，请指定表的模式，而不是用户名。
将在未来版本的SQL Server中删除将TYPE指定为级别0类型的能力。
对于TYPE，使用SCHEMA作为0级类型，使用TYPE作为1级类型。

We do not recommend specifying USER as a level 0 type when you apply an extended property to a database object, because this can cause name resolution ambiguity. For example, assume user Mary owns two schemas (Mary and MySchema) and these schemas both contain a table named MyTable. If Mary adds an extended property to table MyTable and specifies @level0type = N'USER', @level0name = Mary, it is not clear to which table the extended property is applied. To maintain backward compatibility, SQL Server will apply the property to the table that is contained in the schema named Mary.

当将扩展属性应用于数据库对象时，我们不建议将USER指定为级别0类型，因为这可能会导致名称解析模糊。
例如，假设用户Mary拥有两个模式（Mary和MySchema），这些模式都包含一个名为MyTable的表。
如果Mary向表MyTable添加扩展属性并指定@ level0type = N'USER'，@ level0name = Mary，则不清楚扩展属性应用于哪个表。
为了保持向后兼容性，SQL Server会将该属性应用于包含在名为Mary的模式中的表。

总结来说，当将扩展属性应用于数据库对象时，微软推荐使用 SCHEMA，不推荐 USER，但是 USER 还是会保留，还是可以使用但是在某些情况下（如上所述）可能会出现自己意料之外的事情。

示例

-- 查询
SELECT  [TypeId] ,  [TypeName]
FROM    [dbo].[tabBlockType];

-- USER （不推荐）
-- 添加表 tabBlockType 描述
EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型表', N'USER', N'dbo',
    N'table', N'tabBlockType';

-- 添加列 TypeId 描述
EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型id', N'USER', N'dbo',
    N'TABLE', N'tabBlockType', N'COLUMN', N'TypeId';
-- 添加 列 TypeName 描述
EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型名称', N'USER', N'dbo',
    N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';

-- 更新列 TypeName 描述
EXECUTE sp_updateextendedproperty N'MS_Description', N'黑名单类型', N'USER', N'dbo',
    N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';

-- SCHEMA （推荐）
-- 添加表 tabBlockType 描述
EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型表', N'SCHEMA', N'dbo',
    N'table', N'tabBlockType';

-- 添加列 TypeId 描述
EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型id', N'SCHEMA', N'dbo',
    N'TABLE', N'tabBlockType', N'COLUMN', N'TypeId';
-- 添加 列 TypeName 描述
EXECUTE sp_addextendedproperty N'MS_Description', N'黑名单类型名称', N'SCHEMA', N'dbo',
    N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';

-- 更新列 TypeName 描述
EXECUTE sp_updateextendedproperty N'MS_Description', N'黑名单类型', N'SCHEMA', N'dbo',
    N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';

-- 创建 Create sql 脚本 生成的添加描述 脚本

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'黑名单类型id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tabBlockType', @level2type=N'COLUMN',@level2name=N'TypeId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'黑名单类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tabBlockType', @level2type=N'COLUMN',@level2name=N'TypeName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'黑名单类型表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tabBlockType'
GO

sql 总览

ms_desc_summary

创建create 语句时 描述信息也会导出

ms_desc0

More

参考资料

• https://msdn.microsoft.com/en-us/library/ms180047.aspx
• https://msdn.microsoft.com/en-us/library/ms186885.aspx
• http://www.cnblogs.com/wangshenhe/p/3178039.html (注：这篇博客中有几点错误的地方，推荐看上面两篇，官方文档最准)

In the end

笔者水平有限，如果发现有什么错误的地方，欢迎指出，欢迎与我联系 ben121011@126.com