sp_updateextendedproperty

sp_updateextendedproperty#

Updates the value of an existing extended property.

Syntax#

sp_updateextendedproperty
    [ @name = ] N'name'
    [ , [ @value = ] value ]
    [ , [ @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 updated. name is sysname, with no default.

[ @value = ] value

The value associated with the property. value is sql_variant, with a default of NULL. The size of value can’t be more than 7,500 bytes.

[ @level0type = ] ‘level0type’

The user or user-defined type. 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 names of the higher level objects that own or contain them.

Given a valid name and value, if all object types and names are NULL, the property updated belongs to the current database.

Examples#

A. Update an extended property on a database#

The following example first creates an extended property on the current database and then updates the value of that property.

EXECUTE sp_updateextendedproperty
    @name = N'NewCaption',
    @value = 'Sample Database';

B. Update an extended property on a column#

The following example updates the value of property Caption on column ID in table T1.

EXECUTE sp_updateextendedproperty
    @name = N'Caption',
    @value = 'Employee ID must be unique.',
    @level0type = 'SCHEMA',
    @level0name = N'dbo',
    @level1type = 'TABLE',
    @level1name = N'T1',
    @level2type = 'COLUMN',
    @level2name = N'id';

See Also#

Contents