Viewing Extended Properties

Extended properties on database objects can be displayed by using either the fn_listextendedproperty built-in function or the sys.extended_properties catalog view in a SQL statement. Also, extended properties can be viewed by using the SQL Server Management Studio Object Explorer.

Using the fn_listextendedproperty Function

The fn_listextendedproperty function can display extended properties on a single database object or all objects in the database, based on the object type. For example, you can return the extended properties on a table or all columns in the table.

The following examples demonstrate how to display extended properties for a variety of objects by using the fn_listextendedproperty function in a SELECT statement.

A. Displaying extended properties on a database

The following example displays all extended properties set on the database itself.

USE AdventureWorks;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default, default, default, default);
GO

Here is the result set:

objtype    objname     name            value
---------  ---------   -----------     ----------------------------
NULL       NULL        MS_Description  AdventureWorks Sample OLTP Database

(1 row(s) affected)

B. Displaying extended properties on all columns in a table

The following example displays extended properties for columns in the ScrapReason table that is contained in the schema Production.

USE AdventureWorks;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', default);
GO

Here is the result set:

objtype objname      name            value   
------- -----------  -------------   ------------------------
COLUMN ScrapReasonID MS_Description  Primary key for ScrapReason records.
COLUMN Name          MS_Description  Failure description.
COLUMN ModifiedDate  MS_Description  Date the record was last updated.

(3 row(s) affected)

C. Displaying extended properties on all tables in a schema

The following example displays extended properties for all the tables that are contained in the Sales schema.

USE AdventureWorks;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', default, NULL, NULL);
GO

Using the sys.extended_properties Catalog View

Extended properties can be displayed by querying the sys.extended_properties catalog view. The view provides a simple method for returning extended property values and can be combined with other catalog views to return additional data such as the object names or object types associated with the extended property.

The following examples demonstrate how to display extended properties for a variety of objects by using the sys.extended_properties catalog views. Joins on additional catalog views return object names such as column names in a table.

A. Displaying all extended properties in a database

The following example displays all extended properties in the AdventureWorks sample database.

USE AdventureWorks;
GO
SELECT class, class_desc, major_id, minor_id, name, value
FROM sys.extended_properties;
GO

B. Displaying extended properties for all indexes in a database

The following example displays all extended properties for the indexes in the AdventureWorks sample database.

USE AdventureWorks;
GO
SELECT class, class_desc, major_id, minor_id, ep.name, s.name AS [Index Name], value
FROM sys.extended_properties AS ep
INNER JOIN sys.indexes AS s ON ep.major_id = s.object_id AND ep.minor_id = s.index_id
WHERE class = 7;
GO

C. Displaying extended properties for columns in a table

The following example displays extended properties for all columns in all the tables in the AdventureWorks sample database.

USE AdventureWorks;
GO
SELECT major_id, minor_id, t.name AS [Table Name], c.name AS [Column Name], value AS [Extended Property]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id 
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class = 1;
GO

Using Object Explorer to Display Extended Properties

Extended properties can be displayed, added, and deleted by using the Object Explorer in SQL Server Management Studio. Extended properties can only be displayed for the selected object. To display the extended properties of an object using Object Explorer, follow these steps:

  1. From Object Explorer, right-click the database object and select Properties.
  2. From the Properties dialog box, select Extended Properties. The extended property is displayed in the Properties pane. Existing properties can be deleted and new properties can be added from this dialog box.

See Also

Concepts

Using Extended Properties on Database Objects

Other Resources

sys.extended_properties (Transact-SQL)
Using Object Explorer

Help and Information

Getting SQL Server 2005 Assistance