Using Extended Properties on Database Objects

In using extended properties, you can add text, such as descriptive or instructional content, add input masks, and add formatting rules as properties of objects in a database or of the database itself. For example, you can add an extended property to a schema, a schema's view, or to a column in the view. Because extended properties are stored in the database, all applications reading the properties can evaluate the object in the same way. This helps enforce consistency in the way data is treated by all the programs in the system.

Extended properties can be used for the following:

• Specifying a caption for a table, view, or column. Applications can then use the same caption in a user interface that displays information from that table, view, or column.
• Specifying an input mask for a column so that applications can validate data before running a Transact-SQL statement. For example, the required format for a postal code or telephone number column can be specified in the extended property.
• Specifying formatting rules for displaying the data in a column.
• Recording a description of specific database objects that applications can display to users. For example, the descriptions may be used in a data dictionary application or report.
• Specifying the size and window location at which a column should be displayed.

Note

Extended properties should not be used to hide sensitive information about an object. Any user who has been granted permission on the object will be able to view the extended properties on that object. For example, if you grant a user SELECT permission on a table, the user will be able to view the extended properties on that table.

Defining Extended Properties

Each extended property has a user-defined name and value. The value of an extended property is a sql_variant value that can contain up to 7,500 bytes of data. Multiple extended properties can be added to a single object.

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 one 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, which contains the table.

In the following example, the extended property value 'Minimum inventory quantity.' is added to the SafetyStockLevel column in the Product table that is contained in the Production schema.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Minimum inventory quantity.', 
@level0type = N'SCHEMA', @level0name = Production, 
@level1type = N'TABLE',  @level1name = Product,
@level2type = N'COLUMN', @level2name = SafetyStockLevel;
GO

Adding Extended Properties to Objects

The following tables list objects to which you can add extended properties. Their valid level 0, level 1, and level 2 object types are listed and also the permissions required to add, drop, or view the extended properties.

Aggregate

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER			SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.
1	AGGREGATE	ALTER on aggregate OBJECT	Any on OBJECT	Applies to user-defined CLR aggregate functions.

Assembly

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	ASSEMBLY	ALTER on ASSEMBLY	Any on ASSEMBLY
1	None
2	None

Contract (Service Broker)

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	CONTRACT	ALTER on CONTRACT	Any on CONTRACT
1	None
2	None

Database

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	None	ALTER on DATABASE	CONTROL on DATABASE Or TAKE OWNERSHIP on DATABASE Or VIEW DEFINITION on DATABASE Or ALTER ANY DATABASE on SERVER Or CREATE DATABASE on SERVER	Property applies to the database itself.
1	None
2	None

Example

In the following example, an extended property is added to the AdventureWorks sample database itself.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'AdventureWorks Sample OLTP Database';
GO

Default

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER
1	DEFAULT	ALTER on SCHEMA	HAS_DBACCESS = 1	Provided for backward compatibility only. For more information, see Deprecated Database Engine Features in SQL Server 2005.
2	None

Event Notification

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	EVENT NOTIFICATION	ALTER ANY DATABASE EVENT NOTIFICATION ON DATABASE	Owner of event notification Or CONTROL, ALTER, TAKE OWNERSHIP, or VIEW DEFINITION on the database Or ALTER ANY DATABASE EVENT NOTIFICATION	Applies to database-level event notifications that are parented by the database. Extended properties cannot be added to server-level event notifications.
1	None
2	None

Filegroup or File Name

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	FILEGROUP	ALTER on DATABASE	HAS_DBACCESS = 1
1	LOGICAL FILE NAME	ALTER on DATABASE	HAS_DBACCESS = 1
2	None

Example

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

In the following example, an extended property is added to the logical file name AdventureWorks_Data in the PRIMARY filegroup.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Primary data file for the AdventureWorks sample database.', 
@level0type = N'FILEGROUP', @level0name = [PRIMARY],
@level1type = N'Logical File Name', @level1name = AdventureWorks_Data;
GO

Function

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER			SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.
1	FUNCTION	ALTER on function OBJECT	Any on COLUMN Or Any on OBJECT	Applies to these user-defined functions: CLR scalar CLR table-valued Transact-SQL scalar Transact-SQL inline table-valued Transact-SQL table-valued If a level 2 type is not specified, the property applies to the function itself.
2	COLUMN	ALTER on function OBJECT	Any on COLUMN Or Any on OBJECT
2	CONSTRAINT	ALTER on function OBJECT	Any on COLUMN Or Any on OBJECT
2	PARAMETER	ALTER on function OBJECT	Any on COLUMN Or Any on OBJECT

Example

In the following example, an extended property is added to the user-defined function ufnGetStock.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Scalar function returning the quantity of inventory for a specified ProductID.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = ufnGetStock;
GO

In the following example, an extended property is added to the parameter @ProductID defined in the user-defined function ufnGetStock.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Input parameter. Enter a valid ProductID.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = ufnGetStock,
@level2type = N'PARAMETER', @level2name ='@ProductID';
GO

Message Type

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	MESSAGE TYPE	ALTER on MESSAGE TYPE	HAS_DBACCESS = 1
1	None
2	None

Partition Function

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	PARTITION FUNCTION	ALTER ANY DATASPACE on DATABASE	HAS_DBACCESS = 1
1	None
2	None

Partition Scheme

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	PARTITION SCHEME	ALTER ANY DATASPACE on DATABASE	HAS_DBACCESS = 1
1	None
2	None

Procedure

Level

Level type

Permissions required to add or drop an extended property

Permissions required to view an extended property

Comments

0

SCHEMA or USER

 

 

SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.

1

PROCEDURE

ALTER on procedure OBJECT

Note:

Only members of the sysadmin fixed server role can add extended properties to extended stored procedures.

Any on OBJECT

Applies to these user-defined stored procedures:

• CLR
• Transact-SQL
• Replication filter

If a level 2 type is not specified, the property applies to the procedure itself.

2

PARAMETER

ALTER on procedure OBJECT

Any on OBJECT

 

Queue (Service Broker)

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER			SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.
1	QUEUE	ALTER on queue OBJECT	Any on OBJECT
2	EVENT NOTIFICATION	ALTER on queue OBJECT	Owner of event notification Or CONTROL, ALTER, TAKE OWNERSHIP, or VIEW DEFINITION on the queue Or ALTER ANY DATABASE EVENT NOTIFICATION	The event notification may have a separate owner from the queue.

Remote Service Binding (Service Broker)

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	REMOTE SERVICE BINDING	ALTER on REMOTE SERVICE BINDING	Any on REMOTE SERVICE BINDING
1	None
2	None

Route (Service Broker)

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	ROUTE	ALTER on REMOTE SERVICE BINDING	Any on ROUTE
1	None
2	None

Rule

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER
1	RULE	ALTER on SCHEMA	HAS_DBACCESS = 1	Provided for backward compatibility. For more information, see Deprecated Database Engine Features in SQL Server 2005.
2	None

Schema or User

Level

Level type

Permissions required to add or drop an extended property

Permissions required to view an extended property

Comments

0

SCHEMA or USER

ALTER on SCHEMA or ALTER on USER

Any on APPLICATION ROLE

Or

Any on ROLE

Or

Any ON USER

If a level 1 type is not specified, the property applies to the schema or user itself.

USER can be one of the following:

• Application Role
• Database Role
• SQL Server User
• Windows Group
• Windows User

USER should only be used when you add an extended property to a user.

Important:

USER as a level 0 type defined on level 1 or level 2 type objects will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use SCHEMA as the level 0 type instead.

1

Varies

 

 

 

2

Varies

 

 

 

Service

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	SERVICE	ALTER on SERVICE	ALTER on SERVICE Or CONTROL on SERVICE Or TAKE OWNERSHIP on SERVICE Or VIEW DEFINITION on SERVICE
1	None
2	None

Synonym

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER			SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.
1	SYNONYM	CONTROL on SYNONYM	Any on OBJECT
2	None

Table

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER			SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.
1	TABLE	ALTER on table OBJECT	Any on COLUMN Or Any on OBJECT	Applies to user-defined tables. If a level 2 type is not specified, the property applies to the table itself.
2	COLUMN	ALTER on table OBJECT	Any on COLUMN Or Any on OBJECT
2	CONSTRAINT	ALTER on table OBJECT	ALTER on OBJECT Or CONTROL on OBJECT Or TAKE OWNERSHIP on OBJECT Or VIEW DEFINITION on OBJECT	Applies to these constraints: CHECK DEFAULT FOREIGN KEY PRIMARY KEY UNIQUE
2	INDEX	ALTER on table OBJECT	Any on COLUMN Or Any on OBJECT	Applies to relational and XML indexes.
2	TRIGGER	ALTER on table OBJECT	ALTER on OBJECT Or CONTROL on OBJECT Or TAKE OWNERSHIP on OBJECT Or VIEW DEFINITION on OBJECT	Applies to these DML triggers: CLR Transact-SQL Does not apply to DDL triggers.

Example

In the following example, an extended property is added to the Address table in the Person schema.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'Street address information for customers, employees, and vendors.', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Address;
GO

In the following example, an extended property is added to the index IX_Address_StateProviceID on the Address table in the Person schema.

USE AdventureWorks;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Nonclustered index on StateProvinceID.', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Address,
@level2type = N'INDEX',  @level2name = IX_Address_StateProvinceID;
GO

Trigger

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	TRIGGER	ALTER ANY TRIGGER on DATABASE	ALTER ANY TRIGGER on DATABASE	Applies to DDL triggers only.
1	None
2	None

Type

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	TYPE	CONTROL on TYPE	Any on TYPE	Applies to user-defined types created in earlier versions of SQL Server. Provided for backward compatibility. For more information, see Deprecated Database Engine Features in SQL Server 2005.
1	None
2	None

Type

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER			SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.
1	TYPE	CONTROL on TYPE	Any on TYPE
2	None

View

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER			SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.
1	VIEW	ALTER on view OBJECT	Any on COLUMN Or Any on OBJECT	Applies to user-defined views. If a level 2 type is not specified, the property applies to the view itself.
2	COLUMN	ALTER on view OBJECT	Any on COLUMN Or Any on OBJECT
2	INDEX	ALTER on view OBJECT	Any on COLUMN Or Any on OBJECT	Applies to relational and XML indexes.
2	TRIGGER	ALTER on view OBJECT	ALTER ANY TRIGGER on DATABASE Or ALTER on OBJECT Or CONTROL on OBJECT Or TAKE OWNERSHIP on OBJECT Or VIEW DEFINITION on OBJECT	Applies to these DML triggers: CLR Transact-SQL Does not apply to DDL triggers.

XML Schema Collection

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property	Comments
0	SCHEMA or USER			SCHEMA should be used instead of USER. See "Schema vs. User" later in this topic.
1	XML SCHEMA COLLECTION	ALTER on XML SCHEMA COLLECTION	ALTER on XML SCHEMA COLLECTION Or CONTROL on XML SCHEMA COLLECTION Or REFERENCES on XML SCHEMA COLLECTION Or TAKE OWNERSHIP on XML SCHEMA COLLECTION Or VIEW DEFINITION on XML SCHEMA COLLECTION	Extended properties cannot be added to an XML namespace.
2	None

Objects with No Extended Property Support

Extended properties cannot be defined on these objects:

• Database-scope objects not listed in the previous tables. This includes Full-text objects.
• Objects outside the database scope such as HTTP end points.
• Unnamed objects such as partition function parameters.
• Certificates, symmetric keys, asymmetric keys, and credentials.
• System-defined objects such as system tables, catalog views, and system stored procedures.

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 that are independent of the users who own the schemas.

You should note that in SQL Server 2005, if you specify USER as a level 0 type when you apply an extended property to a database object, it 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.

Using Input Mask vs. CHECK Constraints

Both CHECK constraints and extended properties that provide an input-mask can be used to specify the pattern of data expected for table or view columns. Most sites choose one or the other unless either of the following occurs:

• The CHECK constraints were used as an interim measure until all the programs dealing with this table could be changed to use the input mask properties.
• The site also supports users who can update the data through ad hoc tools that do not read the extended properties.

The advantage of the input mask over the CHECK constraint is that the logic is applied in the applications. These can generate more informative errors if a user provides data that is incorrectly formatted. The disadvantage of the input mask is that it requires a separate call to fn_listextendedproperty or sys.extended_properties to obtain the property, and the logic to enforce the mask must be added in all programs.

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.

Using Extended Properties in Applications

Extended properties provide only a named location in which to store data. All applications must be coded to query the property and take appropriate action. For example, adding a caption property to a column does not create a caption that can be displayed by an application. Each application must be coded to read the caption and display it correctly.

To add an extended property

• sp_addextendedproperty (Transact-SQL)

To update an extended property

• sp_updateextendedproperty (Transact-SQL)

To delete an extended property

• sp_dropextendedproperty (Transact-SQL)

To view an extended property

• fn_listextendedproperty (Transact-SQL)
• sys.extended_properties (Transact-SQL)

See Also

Concepts

Permissions

Other Resources

Designing Databases
HAS_DBACCESS (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
5 December 2005	New content: Added the section "Replicating Extended Properties." Added a note in the Procedure section regarding the permissions that are required for extended stored procedures.