Using Extended Properties on Database Objects

SQL Server 2008 Books Online (October 2009)

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.

Asymmetric Key

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

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

Certificate

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	CERTIFICATE	ALTER on CERTIFICATE	Any on CERTIFICATE
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

Level

Level type

Permissions required to add or drop an extended property

Permissions required to view an extended property

Comments

None

ALTER on DATABASE

CONTROL on DATABASE

TAKE OWNERSHIP on DATABASE

VIEW DEFINITION on DATABASE

ALTER ANY DATABASE on SERVER

CREATE DATABASE on SERVER

Property applies to the database itself.

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 2008.
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

Level

Level type

Permissions required to add or drop an extended property

Permissions required to view an extended property

Comments

EVENT NOTIFICATION

ALTER ANY DATABASE EVENT NOTIFICATION ON DATABASE

Owner of event notification

CONTROL, ALTER, TAKE OWNERSHIP, or VIEW DEFINITION on the database

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.

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

Plan Guide

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	PLAN GUIDE	ALTER ANY DATASPACE on DATABASE	VIEW DEFINITION, ALTER, TAKE OWNERSHIP, or CONTROL on Database for SQL or TEMPLATE guides, and VIEW DEFINITION, ALTER, TAKE OWNERSHIP or CONTROL on object for OBJECT guides
1	None
2	None

The following example added an extended property to the plan guide TemplateGuide1.

USE AdventureWorks;
GO
EXECUTE sp_addextendedproperty @name=N'PlanGuideGroup', @value = N'MyTest', 
@level0type = N'Plan Guide', @level0name = 'TemplateGuide1';

Procedure

Level

Level type

Permissions required to add or drop an extended property

Permissions required to view an extended property

Comments

SCHEMA or USER

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

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.

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

SCHEMA or USER

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

QUEUE

ALTER on queue OBJECT

Any on OBJECT

EVENT NOTIFICATION

ALTER on queue OBJECT

Owner of event notification

CONTROL, ALTER, TAKE OWNERSHIP, or VIEW DEFINITION on the queue

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 2008.
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

Level

Level type

Permissions required to add or drop an extended property

Permissions required to view an extended property

Comments

SCHEMA or USER

ALTER on SCHEMA or ALTER on USER

Any on APPLICATION ROLE

Any on ROLE

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.

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

Level

Level type

Permissions required to add or drop an extended property

Permissions required to view an extended property

SERVICE

ALTER on SERVICE

CONTROL on SERVICE

TAKE OWNERSHIP on SERVICE

VIEW DEFINITION on SERVICE

None

Symmetric Key

Level	Level type	Permissions required to add or drop an extended property	Permissions required to view an extended property
0	SYMMETRIC KEY	ALTER on CERTIFICATE	Any on KEY
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 2008.
1	None
2	None

Level

Level type

Permissions required to add or drop an extended property

Permissions required to view an extended property

Comments

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 2008.

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

SCHEMA or USER

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

XML SCHEMA COLLECTION

ALTER on XML SCHEMA COLLECTION

CONTROL on XML SCHEMA COLLECTION

REFERENCES on XML SCHEMA COLLECTION

TAKE OWNERSHIP on XML SCHEMA COLLECTION

VIEW DEFINITION on XML SCHEMA COLLECTION

Extended properties cannot be added to an XML namespace.

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. Database objects are now contained in schemas that are independent of the users who own the schemas.

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

Other Resources

Designing Databases
Permissions (Database Engine)
HAS_DBACCESS (Transact-SQL)

Help and Information

Getting SQL Server 2008 Assistance