SqlServer創建數據表描述及列描述信息 Intro Q: 為什麼要創建描述信息？ A: 滑鼠懸停在對應表和列上時，會出現描述信息，可以提高工作的效率，藉助工具我們根據數據表可以生成Model，可以將描述信息。添加描述語法添加描述存儲過程【sp_addextendedproperty】語法 ...

SqlServer創建數據表描述及列描述信息

Intro

Q: 為什麼要創建描述信息？

A: 滑鼠懸停在對應表和列上時，會出現描述信息，可以提高工作的效率，藉助工具我們根據數據表可以生成Model，可以將描述信息。

添加描述語法

添加描述存儲過程【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 還是會保留，還是可以使用但是在某些情況下（如上所述）可能會出現自己意料之外的事情。

示例

 1 -- 查詢
 2 SELECT  [TypeId] ,  [TypeName]
 3 FROM    [dbo].[tabBlockType];
 4 
 5 -- USER （不推薦）
 6 -- 添加表 tabBlockType 描述
 7 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名單類型表', N'USER', N'dbo',
 8     N'table', N'tabBlockType';
 9 
10 -- 添加列 TypeId 描述
11 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名單類型id', N'USER', N'dbo',
12     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeId';
13 -- 添加 列 TypeName 描述
14 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名單類型名稱', N'USER', N'dbo',
15     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';
16 
17 -- 更新列 TypeName 描述
18 EXECUTE sp_updateextendedproperty N'MS_Description', N'黑名單類型', N'USER', N'dbo',
19     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';
20 
21 -- SCHEMA （推薦）
22 -- 添加表 tabBlockType 描述
23 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名單類型表', N'SCHEMA', N'dbo',
24     N'table', N'tabBlockType';
25 
26 -- 添加列 TypeId 描述
27 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名單類型id', N'SCHEMA', N'dbo',
28     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeId';
29 -- 添加 列 TypeName 描述
30 EXECUTE sp_addextendedproperty N'MS_Description', N'黑名單類型名稱', N'SCHEMA', N'dbo',
31     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';
32 
33 -- 更新列 TypeName 描述
34 EXECUTE sp_updateextendedproperty N'MS_Description', N'黑名單類型', N'SCHEMA', N'dbo',
35     N'TABLE', N'tabBlockType', N'COLUMN', N'TypeName';
36 
37 -- 創建 Create sql 腳本 生成的添加描述 腳本
38 
39 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'
40 GO
41 
42 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'
43 GO
44 
45 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'黑名單類型表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tabBlockType'
46 GO