Using Extended Properties on Database Objects

Microsoft® SQL Server™ 2000 introduces extended properties that users can define on various objects in a database. These extended properties can be used to store application-specific or site-specific information about the database objects. Because the property is stored in the database, all applications reading the property can evaluate the object in the same way. This helps enforce consistency in the way data is treated by all of the programs in the system.

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. Individual database objects can have multiple extended properties.

Possible uses of extended properties include:

Specifying a caption for a table, view, or column. All 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 all applications can validate data before executing a Transact-SQL statement.
Specifying formatting rules for displaying the data in a column.
Recording a description of specific database objects that applications can display to users.
Specifying the size and window location at which a column should be displayed.

For the purposes of specifying extended properties, the objects in a SQL Server 2000 database are classified into three levels (0, 1, 2). Level 0 is the highest level and 2 is the lowest level. The table lists the level-0 objects, user and user-defined data type, with their valid level-1 and level-2 objects.

Level 0	Level 1	Level 2
User	Table	Column, index, constraint, trigger
	View	Column, INSTEAD OF trigger
	Schema-bound view	Column, index, INSTEAD OF trigger
	Stored procedure	Parameter
	Rule	<None>
	Default	<None>
	Function	Column, parameter, constraint,
	Schema-bound function	Column, parameter, constraint
User-defined data type	<None>	<None>

Extended properties are not supported on objects that are not listed as level 0, 1, or 2 objects.

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 referencing a column (level 2) you must also specify the table (level 1) that contains the column and the user (level 0) who owns the table.

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

Extended properties are managed using three system stored procedures:

sp_addextendedproperty
Adds a new extended property to a database object.
sp_updateextendedproperty
Updates the value of an existing extended property.
sp_dropextendedproperty
Drops an existing extended property.

You can retrieve the value of an existing extended property using the system function FN_LISTEXTENDEDPROPERTY.

The following is an example of a table that has:

Caption extended properties for the table and the columns.
Input-mask extended properties for the columns.

USE Northwind
GO
CREATE TABLE TestExProp
  (PriKey      int PRIMARY KEY IDENTITY(1,1),
   USPhoneNmbr      char(13)
        CHECK (USPhoneNmbr LIKE
               '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
               ),
   USSocialScrty   char(11)
        CHECK (USSocialScrty LIKE
               '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
               )
  )
GO
sp_addextendedproperty 'Caption', 'Caption Test Table',
                       'user', dbo, 'table', TestExProp
GO
sp_addextendedproperty 'Caption', 'Primary Key',
                       'user', dbo, 'table', TestExProp, 'column', PriKey
GO
sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
                       'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Caption', 'US Phone Number',
                       'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
                       'user', dbo, 'table', TestExProp, 'column', USSocialScrty
GO
sp_addextendedproperty 'Caption', 'US Social Security Number',
                       'user', dbo, 'table', TestExProp, 'column', USSocialScrty
GO

This statement updates the primary-key caption property:

sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
                       'user', dbo, 'table', TestExProp, 'column', PriKey

This statement drops the input-mask properties:

sp_dropextendedproperty 'Input Mask',
                       'user', dbo, 'table', TestExProp,
                       'column', USSocialScrty
GO
sp_dropextendedproperty 'Input Mask',
                       'user', dbo, 'table', TestExProp,
                       'column', USPhoneNmbr
GO

This statement retrieves the table-caption property:

SELECT *
FROM ::FN_LISTEXTENDEDPROPERTY('Caption', 'User','dbo','table',
                               'TestExProp', default, default)

The example shows using CHECK constraints and an input-mask property to specify the pattern of data for each column. Most sites choose one or the other unless:

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, which can generate more informative errors if a user provides improperly formatted data. The disadvantage of the input mask is that it requires a separate call to fn_listextendedproperty to obtain the property, and the logic to enforce the mask must be added in all programs.

Using Extended Properties on Database Objects

See Also