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

ms190243.note(en-US,SQL.90).gifNote:

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.

ms190243.note(en-US,SQL.90).gifImportant:

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

To update an extended property

To delete an extended property

To view an extended property

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.