sp_dropextendedproperty (Transact-SQL)sp_dropextendedproperty (Transact-SQL)

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

卸除現有的擴充屬性。Drops an existing extended property.

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

語法Syntax

  
sp_dropextendedproperty   
    [ @name = ] { 'property_name' }  
      [ , [ @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 dropped. property_namesysname不能是 NULL。property_name is sysname and cannot be NULL.

[ @level0type=] {'level0_object_type&lt'}[ @level0type= ]{ 'level0_object_type'}
這是所指定之層級 0 物件類型的名稱。Is the name of the level 0 object type specified. 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 和 NULL。Valid inputs are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, 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'}
這是所指定之層級 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. 有效輸入如下: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資料庫分為三個層級: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 types and names of all higher level objects.

指定的有效property_name,如果所有物件類型和名稱都是 null,且有屬性存在於目前的資料庫,會刪除此屬性。Given a valid property_name, if all object types and names are null and a property exists on the current database, that property is deleted. 請參閱本主題稍後的範例 B。See example B that follows later in this topic.

PermissionsPermissions

db_owner 和 db_ddladmin 固定資料庫角色的成員可卸除任何物件的擴充屬性,但下列為例外狀況:db_ddladmin 不能將屬性加入至資料庫本身或加入至使用者或角色。Members of the db_owner and db_ddladmin fixed database roles may drop 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 drop extended properties to objects they own or on which they have ALTER or CONTROL permissions.

範例Examples

A.A. 卸除資料行的擴充屬性Dropping an extended property on a column

下列範例會從 caption 結構描述所包含之 id 資料表中的資料行 T1 中移除 dbo 屬性。The following example removes the property caption from column id in table T1 contained in the schema dbo.

CREATE TABLE T1 (id int , name char (20));  
GO  
EXEC sp_addextendedproperty   
     @name = 'caption'   
    ,@value = 'Employee ID'   
    ,@level0type = 'schema'   
    ,@level0name = dbo  
    ,@level1type = 'table'  
    ,@level1name = 'T1'  
    ,@level2type = 'column'  
    ,@level2name = id;  
GO  
EXEC sp_dropextendedproperty   
     @name = 'caption'   
    ,@level0type = 'schema'   
    ,@level0name = dbo  
    ,@level1type = 'table'  
    ,@level1name = 'T1'  
    ,@level2type = 'column'  
    ,@level2name = id;  
GO  
DROP TABLE T1;  
GO  

B.B. 卸除資料庫的擴充屬性Dropping an extended property on a database

下列範例會移除名為的屬性MS_DescriptionAdventureWorks2012AdventureWorks2012範例資料庫。The following example removes the property named MS_Description from the AdventureWorks2012AdventureWorks2012 sample database. 由於此屬性位於資料庫本身,因此未指定任何物件類型和名稱。Because the property is on the database itself, no object types and names are specified.

USE AdventureWorks2012;  
GO  
EXEC sp_dropextendedproperty   
@name = N'MS_Description';  
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_updateextendedproperty (-SQL)) sp_updateextendedproperty (Transact-SQL)
sys.extended_properties (-SQL))sys.extended_properties (Transact-SQL)