sp_addextendedproperty (Transact-SQL)

Article
12/03/2008

Adds a new extended property to a database object.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_addextendedproperty
    [ @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' }
Is the name of the property to be added. property_name is sysname and cannot be NULL. Names can also include blank or non-alphanumeric character strings, and binary values.

[ @value= ] { 'value' }
Is the value to be associated with the property. value is sql_variant, with a default of NULL. The size of value cannot be more than 7,500 bytes.

[ @level0type= ] { 'level0_object_type' }
Is the type of level 0 object. level0_object_type is varchar(128), with a default of 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.

Important

USER and TYPE as level-0 types will be removed in a future version of SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features. Use SCHEMA as the level 0 type instead of USER. For TYPE, use SCHEMA as the level 0 type and TYPE as the level 1 type.

[ @level0name= ] { 'level0_object_name' }
Is the name of the level 0 object type specified. level0_object_name is sysname with a default of NULL.

[ @level1type = ] { 'level1_object_type' }
Is the type of level 1 object. level1_object_type is varchar(128), with a default of NULL. Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.

[ @level1name= ] { 'level1_object_name' }
Is the name of the level 1 object type specified. level1_object_name is sysname, with a default of NULL.

[ @level2type= ] { 'level2_object_type' }
Is the type of level 2 object. level2_object_type is varchar(128), with a default of NULL. Valid inputs are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.

[ @level2name= ] { 'level2_object_name' }
Is the name of the level 2 object type specified. level2_object_name is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks

For specifying 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 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.

For a complete list of objects and their valid level 0, 1, and 2 types, see Using Extended Properties on Database Objects.

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

Extended properties are not allowed on system objects, objects outside the scope of a user-defined database, or objects not listed in Arguments as valid inputs.

Replicating Extended Properties

Extended properties are replicated only in the initial synchronization between the Publisher and the Subscriber. If you add or modify an extended property after the initial synchronization, the change is not replicated. For more information about replicating database objects, see Publishing Data and Database Objects.

Schema vs. User

In earlier versions of SQL Server, users owned database objects such as tables, views, and triggers. Therefore, adding an extended property to one of these objects and specifying a user name as the Level 0 type was permitted. However, in SQL Server 2005, database objects are contained in schemas. These are independent from the users that own the schemas.

In SQL Server 2005, we do not recommend specifying USER as a level 0 type when you apply an extended property to a database object, because this can cause name resolution ambiguity. For example, assume user Mary owns two schemas (Mary and MySchema) and these schemas both contain a table named MyTable. If Mary adds an extended property to table MyTable and specifies @level0type = N'USER',@level0name = Mary, it is not clear to which table the extended property is applied. To maintain backward compatibility, SQL Server will apply the property to the table that is contained in the schema named Mary. For more information about users and schemas, see User-Schema Separation.

Permissions

Members of the db_owner and db_ddladmin fixed database roles can add extended properties to any object with the following exception: db_ddladmin cannot add properties to the database itself, or to users or roles.

Users can add extended properties to objects they own or have ALTER or CONTROL permissions on. For a complete list of required permissions, see Using Extended Properties on Database Objects.

Examples

A. Adding an extended property to a database

The following example adds the property name 'Caption' with a value of 'AdventureWorks Sample OLTP Database' to the AdventureWorks sample database.

USE AdventureWorks;
GO
--Add a caption to the AdventureWorks Database object itself.
EXEC sp_addextendedproperty 
@name = N'Caption', @value = 'AdventureWorks Sample OLTP Database';

B. Adding an extended property to a column in a table

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

USE AdventureWorks;
GO
EXEC sp_addextendedproperty 
@name = N'Caption', @value = 'Postal code is a required column.',
@level0type = N'Schema', @level0name = Person,
@level1type = N'Table',  @level1name = Address,
@level2type = N'Column', @level2name = PostalCode;
GO

C. Adding an input mask property to a column

The following example adds an input mask property '99999 or 99999-9999 or #### ###' to the column PostalCode in the table Address.

USE AdventureWorks;
GO
EXEC sp_addextendedproperty 
@name = N'Input Mask ', @value = '99999 or 99999-9999 or #### ###',
@level0type = N'Schema', @level0name = Person,
@level1type = N'Table', @level1name = Address, 
@level2type = N'Column',@level2name = PostalCode;
GO

D. Adding an extended property to a filegroup

In the following example, an extended property is added to the PRIMARY filegroup.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Primary filegroup for the AdventureWorks sample database.', 
@level0type = N'FILEGROUP', @level0name = [PRIMARY];
GO

Change History

Release	History
5 December 2005	New content: Added the section Replicating Extended Properties.

Share via