sp_dropextendedproperty

sp_dropextendedproperty#

Drops an existing extended property.

Syntax#

sp_dropextendedproperty
    [ @name = ] N'name'
    [ , [ @level0type = ] 'level0type' ]
    [ , [ @level0name = ] N'level0name' ]
    [ , [ @level1type = ] 'level1type' ]
    [ , [ @level1name = ] N'level1name' ]
    [ , [ @level2type = ] 'level2type' ]
    [ , [ @level2name = ] N'level2name' ]
[;]

Arguments#

[ @name = ] N’name’

The name of the property to be dropped. name is sysname, and can’t be NULL.

[ @level0type = ] ‘level0type’

The name of the level 0 object type specified. level0type is varchar(128), with a default of NULL.

Valid inputs are:

  • SCHEMA

  • NULL

[ @level0name = ] N’level0name’

The name of the level 0 object type specified. level0name is sysname, with a default of NULL.

[ @level1type = ] ‘level1type’

The type of level 1 object. level1type is varchar(128), with a default of NULL.

Valid inputs are:

  • FUNCTION

  • TABLE

  • VIEW

  • NULL

[ @level1name = ] N’level1name’

The name of the level 1 object type specified. level1name is sysname, with a default of NULL.

[ @level2type = ] ‘level2type’

The type of level 2 object. level2type is varchar(128), with a default of NULL.

Valid inputs are:

  • COLUMN

  • CONSTRAINT

  • INDEX

  • NULL

[ @level2name = ] N’level2name’

The name of the level 2 object type specified. level2name is sysname, with a default of NULL.

Remarks#

When you specify extended properties, the objects in a SQL Server database are classified into three levels: 0, 1, and 2. Level 0 is the highest level and is defined as objects contained at the database scope. 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.

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.

Examples#

A. Drop an extended property on a database#

The following example removes the property named MS_Description from the current database. Because the property is on the database itself, no object types and names are specified.

EXECUTE sp_dropextendedproperty @name = N'MS_Description';

B. Drop an extended property on a column#

The following example removes the property caption from column id in table T1 contained in the schema dbo.

EXECUTE sp_dropextendedproperty
    @name = 'caption',
    @level0type = 'SCHEMA',
    @level0name = N'dbo',
    @level1type = 'TABLE',
    @level1name = N'T1',
    @level2type = 'COLUMN',
    @level2name = N'id';

See Also#

Contents