sp_addextendedproperty
Adds a new extended property to a database object. If the property already exists, the procedure fails.
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 may 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 may not be more than 7,500 bytes; otherwise, SQL Server raises an error.
[ @level0type = ] { 'level0_object_type' }
Is the user or user-defined type. level0_object_type is varchar(128), with a default of NULL. Valid inputs are USER, TYPE, and NULL.
[ @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 TABLE, VIEW, PROCEDURE, FUNCTION, DEFAULT, RULE, 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, PARAMETER, INDEX, CONSTRAINT, 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
Extended properties are not allowed on system objects.
The objects are distinguished according to levels, with level 0 as the highest and level 2 the lowest. When a user adds, updates, or deletes an extended property, that user must specify all higher level objects. For example, if the user adds an extended property to a level 1 object, that user must specify all level 0 information. If the user adds an extended property to a level 2 object, all information about levels 0 and 1 must be supplied.
At each level, object type and object name uniquely identify an object. If one part of the pair is specified, the other part must also be specified.
Given a valid property_name and value, if all object types and names are null, then the property belongs to the current database. If an object type and name are specified, then a parent object and type also must be specified. Otherwise, SQL Server raises an error.
Permissions
Members of the db_owner and db_ddladmin fixed database roles may add extended properties to any object. Users may add extended properties to objects they own. However, only db_owner may add properties to user names.
Examples
This example adds the property ('caption,' 'Employee ID') to column 'ID' in table 'T1.'
CREATE table T1 (id int , name char (20))
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo, 'table', T1, 'column', id