sp_addextendedproperty (Transact-SQL)sp_addextendedproperty (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

將新的擴充屬性加入資料庫物件中。Adds a new extended property to a database object.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
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' }   
                        ]   
                ]  
        ]   
    ]   
[;]  

引數Arguments

[ @name ] = {'property_name'}[ @name ] = { 'property_name' }
這是要加入的屬性名稱。Is the name of the property to be added. property_namesysname不能是 NULL。property_name is sysname and cannot be NULL. 名稱也可包含空白或非英數字元字串,以及二進位值。Names can also include blank or non-alphanumeric character strings, and binary values.

[ @value=] {''}[ @value= ] { 'value'}
這是與屬性相關聯的值。Is the value to be associated with the property. sql_variant,預設值是 NULL。value is sql_variant, with a default of NULL. value 的大小不能超過 7,500 個位元組。The size of value cannot be more than 7,500 bytes.

[ @level0type=] {'level0_object_type&lt'}[ @level0type= ] { 'level0_object_type' }
這是層級 0 物件的類型。Is the type of level 0 object. level0_object_type&ltvarchar(128) ,預設值是 NULL。level0_object_type is varchar(128), with a default of NULL.

有效輸入如下:ASSEMBLY、CONTRACT、EVENT NOTIFICATION、FILEGROUP、MESSAGE TYPE、PARTITION FUNCTION、PARTITION SCHEME、REMOTE SERVICE BINDING、ROUTE、SCHEMA、SERVICE、USER、TRIGGER、TYPE、PLAN GUIDE 和 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.

重要

SQL ServerSQL Server將 USER 指定為層級 1 類型物件擴充屬性中之層級 0 類型的功能,將在未來的 版本中移除。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 ServerSQL Server. 請改用 SCHEMA 做為層級 0 類型。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. 將 TYPE 指定為層級 0 類型的功能,將在未來的 SQL ServerSQL Server 版本中移除。The ability to specify TYPE as level-0 type will be removed in a future version of SQL ServerSQL Server. 如果是 TYPE,請使用 SCHEMA 當做層級 0 類型,並使用 TYPE 當做層級 1 類型。For TYPE, use SCHEMA as the level 0 type and TYPE as the level 1 type.

[ @level0name=] {'level0_object_name&lt'}[ @level0name= ] { 'level0_object_name' }
這是所指定之層級 0 物件類型的名稱。Is the name of the level 0 object type specified. level0_object_name&ltsysname預設值是 NULL。level0_object_name is sysname with a default of NULL.

[ @level1type=] {'level1_object_type&lt'}[ @level1type= ] { 'level1_object_type' }
這是層級 1 物件的類型。Is the type of level 1 object. level1_object_type&ltvarchar(128) ,預設值是 NULL。level1_object_type is varchar(128), with a default of NULL. 有效輸入如下,彙總、 預設、 函式、 LOGICAL FILE NAME、 程序、 佇列、 規則、 序列、 同義字、 資料表、 TABLE_TYPE、 類型、 檢視、 XML SCHEMA COLLECTION 和 NULL。Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SEQUENCE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.
[ @level1name=] {'level1_object_name&lt'}[ @level1name= ] { 'level1_object_name' }
這是所指定之層級 1 物件類型的名稱。Is the name of the level 1 object type specified. level1_object_name&ltsysname,預設值是 NULL。level1_object_name is sysname, with a default of NULL.

[ @level2type=] {'level2_object_type&lt'}[ @level2type= ] { 'level2_object_type' }
這是層級 2 物件的類型。Is the type of level 2 object. level2_object_type&ltvarchar(128) ,預設值是 NULL。level2_object_type is varchar(128), with a default of NULL. 有效輸入如下:COLUMN、CONSTRAINT、EVENT NOTIFICATION、INDEX、PARAMETER、TRIGGER 和 NULL。Valid inputs are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.

[ @level2name=] {'level2_object_name&lt'}[ @level2name= ] { 'level2_object_name' }
這是所指定之層級 2 物件類型的名稱。Is the name of the level 2 object type specified. level2_object_name&ltsysname,預設值是 NULL。level2_object_name is sysname, with a default of NULL.

傳回碼值Return Code Values

0 (成功) 或 1 (失敗)0 (success) or 1 (failure)

備註Remarks

為了指定擴充的屬性,在物件SQL ServerSQL Server資料庫分為三個層級:0、 1 和 2。For specifying extended properties, the objects in a SQL ServerSQL Server database are classified into three levels: 0, 1, and 2. 層級 0 是最高層級,且定義為包含在資料庫範圍的物件。Level 0 is the highest level and is defined as objects that are contained at the database scope. 層級 1 物件包含在結構描述或使用者範圍中,層級 2 物件包含在層級 1 物件中。Level 1 objects are contained in a schema or user scope, and level 2 objects are contained by level 1 objects. 任何這些層級的物件都可以定義擴充屬性。Extended properties can be defined for objects at any of these levels.

對一個層級中物件的參考必須用擁有或包含其較高層級物件的名稱來限定。References to an object in one level must be qualified with the names of the higher level objects that own or contain them. 例如,當您將擴充屬性加入至資料表資料行 (層級 2) 時,您也必須指定包含該資料行的資料表名稱 (層級 1) 和包含該資料表的結構描述 (層級 0)。For example, when you add an extended property to a table column (level 2), you must also specify the table name (level 1) that contains the column and the schema (level 0) that contains the table.

如果所有物件類型和名稱都是 NULL,屬性屬於目前資料庫本身。If all object types and names are null, the property belongs to the current database itself.

擴充屬性不得用於系統物件、使用者自訂資料庫範圍外的物件,或未列在引數中做為有效輸入的物件。Extended properties are not allowed on system objects, objects outside the scope of a user-defined database, or objects not listed in Arguments as valid inputs.

記憶體最佳化資料表上不允許擴充的屬性。Extended properties are not allowed on memory-optimized tables.

複寫擴充屬性Replicating Extended Properties

只在發行者與訂閱者之間的初始同步處理中複寫擴充屬性。Extended properties are replicated only in the initial synchronization between the Publisher and the Subscriber. 如果您在初始同步處理之後加入或修改擴充屬性,就不會複寫這項變更。If you add or modify an extended property after the initial synchronization, the change is not replicated. 如需如何複寫資料庫物件的詳細資訊,請參閱發行資料和資料庫物件For more information about how to replicate database objects, see Publish Data and Database Objects.

結構描述與。使用者Schema vs. User

不建議您在將擴充屬性套用至資料庫物件時指定 USER 當做層級 0 類型,因為這會造成名稱解析模稜兩可。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. 例如,假設使用者 Mary 擁有兩個結構描述 (Mary 和 MySchema),而這兩個結構描述都包含一個名為 MyTable 的資料表。For example, assume user Mary owns two schemas (Mary and MySchema) and these schemas both contain a table named MyTable. 如果 Mary 將擴充的屬性加入至 MyTable 資料表,並指定 @level0type = N'USER'@level0name = Mary,並不清楚哪一個資料表擴充的屬性會套用。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. 為了維持回溯相容性,SQL ServerSQL Server 將套用屬性至包含在名為 Mary 之結構描述中的資料表。To maintain backward compatibility, SQL ServerSQL Server will apply the property to the table that is contained in the schema named Mary.

PermissionsPermissions

db_owner 和 db_ddladmin 固定資料庫角色的成員可將擴充屬性加入至任何物件,但下列為例外狀況:db_ddladmin 不能將屬性加入至資料庫本身或加入至使用者或角色。Members of the db_owner and db_ddladmin fixed database roles can add extended properties to any object with the following exception: db_ddladmin cannot add properties to the database itself, or to users or roles.

使用者可將擴充屬性加入至他們擁有的物件或他們具有 ALTER 或 CONTROL 權限的物件。Users can add extended properties to objects they own or have ALTER or CONTROL permissions on.

範例Examples

A.A. 將擴充屬性加入至資料庫Adding an extended property to a database

'Caption' 下列範例將一個含有 'AdventureWorks2012 Sample OLTP Database' 值的屬性名稱 AdventureWorks2012 加入至 範例資料庫。The following example adds the property name 'Caption' with a value of 'AdventureWorks2012 Sample OLTP Database' to the AdventureWorks2012 sample database.

USE AdventureWorks2012;  
GO  
--Add a caption to the AdventureWorks2012 Database object itself.  
EXEC sp_addextendedproperty   
@name = N'Caption',   
@value = 'AdventureWorks2012 Sample OLTP Database';  

B.B. 將擴充屬性加入至資料表中的資料行Adding an extended property to a column in a table

PostalCode 下列範例將標題屬性加入至 Address資料表中的 資料行。The following example adds a caption property to column PostalCode in table Address.

USE AdventureWorks2012;  
GO  
EXEC sp_addextendedproperty   
@name = N'Caption',   
@value = 'Postal code is a required column.',  
@level0type = N'Schema', @level0name = 'Person',  
@level1type = N'Table',  @level1name = 'Address',  
@level2type = N'Column', @level2name = 'PostalCode';  
GO  

C.C. 將輸入遮罩屬性加入至資料行Adding an input mask property to a column

99999 or 99999-9999 or #### ###下列範例將 ' PostalCode ' 輸入遮罩屬性加入至 Address資料表中的 資料行。The following example adds an input mask property '99999 or 99999-9999 or #### ###' to the column PostalCode in the table Address.

USE AdventureWorks2012;  
GO  
EXEC sp_addextendedproperty   
@name = N'Input Mask ', @value = '99999 or 99999-9999 or #### ###',  
@level0type = N'Schema', @level0name = 'Person',  
@level1type = N'Table', @level1name = 'Address',   
@level2type = N'Column',@level2name = 'PostalCode';  
GO  

D.D. 將擴充屬性加入至檔案群組Adding an extended property to a filegroup

PRIMARY 下列範例會將擴充屬性加入至 檔案群組中。The following example adds an extended property to the PRIMARY filegroup.

USE AdventureWorks2012;  
GO  
EXEC sys.sp_addextendedproperty   
@name = N'MS_DescriptionExample',   
@value = N'Primary filegroup for the AdventureWorks2012 sample database.',   
@level0type = N'FILEGROUP', @level0name = 'PRIMARY';  
GO  

E.E. 將擴充屬性加入至結構描述Adding an extended property to a schema

HumanResources 下列範例會將擴充屬性加入至 結構描述中。The following example adds an extended property to the HumanResources schema.

USE AdventureWorks2012;  
GO  
EXECUTE sys.sp_addextendedproperty   
@name = N'MS_DescriptionExample',  
@value = N'Contains objects related to employees and departments.',  
@level0type = N'SCHEMA',   
@level0name = 'HumanResources';  

F.F. 將擴充屬性加入至資料表Adding an extended property to a table

Address 下列範例會將擴充屬性加入至 Person 結構描述中的 資料表。The following example adds an extended property to the Address table in the Person schema.

USE AdventureWorks2012;  
GO  
EXEC sys.sp_addextendedproperty   
@name = N'MS_DescriptionExample',   
@value = N'Street address information for customers, employees, and vendors.',   
@level0type = N'SCHEMA', @level0name = 'Person',  
@level1type = N'TABLE',  @level1name = 'Address';  
GO  

G.G. 將擴充屬性加入至角色Adding an extended property to a role

下列範例會建立應用程式角色並將擴充屬性加入至該角色。The following example creates an application role and adds an extended property to the role.

USE AdventureWorks2012;   
GO  
CREATE APPLICATION ROLE Buyers  
WITH Password = '987G^bv876sPY)Y5m23';   
GO  
EXEC sys.sp_addextendedproperty   
@name = N'MS_Description',   
@value = N'Application Role for the Purchasing Department.',  
@level0type = N'USER',  
@level0name = 'Buyers';  

H.H. 將擴充屬性加入至類型Adding an extended property to a type

下列範例會將擴充屬性加入至類型中。The following example adds an extended property to a type.

USE AdventureWorks2012;   
GO  
EXEC sys.sp_addextendedproperty   
@name = N'MS_Description',   
@value = N'Data type (alias) to use for any column that represents an order number. For example a sales order number or purchase order number.',   
@level0type = N'SCHEMA',   
@level0name = N'dbo',   
@level1type = N'TYPE',   
@level1name = N'OrderNumber';  

I.I. 將擴充屬性加入至使用者Adding an extended property to a user

下列範例會建立使用者並將擴充屬性加入至該使用者。The following example creates a user and adds an extended property to the user.

USE AdventureWorks2012;   
GO  
CREATE USER CustomApp WITHOUT LOGIN ;   
GO  
EXEC sys.sp_addextendedproperty   
@name = N'MS_Description',   
@value = N'User for an application.',   
@level0type = N'USER',   
@level0name = N'CustomApp';  

另請參閱See Also

Database Engine 預存程序(Transact SQL) Database Engine Stored Procedures (Transact-SQL)
sys.fn_listextendedproperty (-SQL)) sys.fn_listextendedproperty (Transact-SQL)
sp_dropextendedproperty (Transact-SQL) sp_dropextendedproperty (Transact-SQL)
sp_updateextendedproperty (-SQL))sp_updateextendedproperty (Transact-SQL)