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:
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:
|
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:
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:
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
Other Resources
Designing Databases
HAS_DBACCESS (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
Release | History |
---|---|
5 December 2005 |
|