sp_addextendedproperty

sp_addextendedproperty#

Adds a new extended property to a database object.

Syntax#

sp_addextendedproperty
    [ @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 added. name is sysname, with no default, and can’t be NULL. Names can include blank or non-alphanumeric character strings, and binary values.

[ @value = ] value

The value to be 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 type of level 0 object. 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.

Return types#

Returns int. 0 (success) or 1 (failure).

Remarks#

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

If all object types and names are null, the property belongs to the current database itself.

Examples#

A. Add an extended property to a database#

The following example adds the property name Caption with a value of ‘Sample OLTP Database’ to the current database.

EXECUTE sp_addextendedproperty
    @name = N'Caption',
    @value = 'Sample OLTP Database';

B. Add an extended property to a schema#

The following example adds an extended property to the HumanResources schema.

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

C. Add an extended property to a table#

The following example adds an extended property to the Address table in the Person schema.

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

D. Add an extended property to a column#

The following example adds a caption property to column PostalCode in table Address.

EXECUTE sp_addextendedproperty
    @name = N'Caption',
    @value = 'Postal code is a required column.',
    @level0type = 'SCHEMA',
    @level0name = N'Person',
    @level1type = 'TABLE',
    @level1name = N'Address',
    @level2type = 'COLUMN',
    @level2name = N'PostalCode';

See Also#

Contents