sp_updateextendedproperty (Transact-SQL)sp_updateextendedproperty (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

更新現有擴充屬性的值。Updates the value of an existing extended property.

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

語法Syntax

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

引數Arguments

[ @name=] {'property_name'}[ @name= ]{ 'property_name'}
這是要更新的屬性名稱。Is the name of the property to be updated. property_namesysname,不能是 NULL。property_name is sysname, and cannot be NULL.

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

[ @level0type=] {'level0_object_type&lt'}[ @level0type= ]{ 'level0_object_type'}
使用者或使用者定義類型。Is the user or user-defined type. level0_object_type&ltvarchar(128) ,預設值是 NULL。level0_object_type is varchar(128), with a default of NULL. 有效輸入如下: 組件、 合約、 EVENT NOTIFICATION、 檔案群組、 訊息類型、 資料分割函數、 資料分割配置、 計畫指南、 REMOTE SERVICE BINDING、 路由、 結構描述、 服務、 使用者、 觸發程序、 類型和 NULL。Valid inputs are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, PLAN GUIDE, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, and NULL.

重要

在未來的 SQL ServerSQL Server 版本中,會移除層級 0 類型的 USER 和 TYPE。USER and TYPE as level-0 types will be removed in a future version of SQL ServerSQL Server. 請避免在新的開發工作中使用這些功能,並規劃修改目前使用這些功能的應用程式。Avoid using these features in new development work, and plan to modify applications that currently use these features. 請改用 SCHEMA 來當做層級 0 類型,而不是使用 USER。Use SCHEMA as the level 0 type instead of USER. 如果是 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'}
這是所指定之層級 1 物件類型的名稱。Is the name of the level 1 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. 有效輸入如下:AGGREGATE、DEFAULT、FUNCTION、LOGICAL FILE NAME、PROCEDURE、QUEUE、RULE、SYNONYM、TABLE、TABLE_TYPE、TYPE、VIEW、XML SCHEMA COLLECTION 和 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&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 資料庫中的物件會分類成 3 個層級 (0、1 和 2)。For the purpose of 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 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.

指定的有效property_name,如果所有物件類型和名稱都是 null,則更新的屬性屬於目前的資料庫。Given a valid property_name and value, if all object types and names are null, the property updated belongs to the current database.

PermissionsPermissions

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

使用者可以更新他們所擁有之物件,或他們有 ALTER 或 CONTROL 權限之物件的擴充屬性。Users may update extended properties to objects they own, or on which they have ALTER or CONTROL permissions.

範例Examples

A.A. 更新資料行的擴充屬性Updating an extended property on a column

下列範例會更新 Caption 資料表 ID 資料行的 T1 屬性值。The following example updates the value of property Caption on column ID in table T1.

USE AdventureWorks2012;  
GO  
CREATE TABLE T1 (id int , name char (20));  
GO  
EXEC sp_addextendedproperty   
    @name = N'Caption'  
    ,@value = N'Employee ID'  
    ,@level0type = N'Schema', @level0name = dbo  
    ,@level1type = N'Table',  @level1name = T1  
    ,@level2type = N'Column', @level2name = id;  
GO  
--Update the extended property.  
EXEC sp_updateextendedproperty   
    @name = N'Caption'  
    ,@value = 'Employee ID must be unique.'  
    ,@level0type = N'Schema', @level0name = dbo  
    ,@level1type = N'Table',  @level1name = T1  
    ,@level2type = N'Column', @level2name = id;  
GO  

B.B. 更新資料庫的擴充屬性Updating an extended property on a database

下列範例會先建立 AdventureWorks2012AdventureWorks2012 範例資料庫的擴充屬性,再更新這個屬性的值。The following example first creates an extended property on the AdventureWorks2012AdventureWorks2012 sample database and then updates the value of that property.

USE AdventureWorks2012;  
GO  
EXEC sp_addextendedproperty   
@name = N'NewCaption', @value = 'AdventureWorks2012 Sample OLTP Database';  
GO  
USE AdventureWorks2012;  
GO  
EXEC sp_updateextendedproperty   
@name = N'NewCaption', @value = 'AdventureWorks2012 Sample Database';  
GO  

另請參閱See Also

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