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 AdventureWorks2008R2;
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 AdventureWorks2008 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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2 sample database.
USE AdventureWorks2008R2;
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 AdventureWorks2008R2 sample database.
USE AdventureWorks2008R2;
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 AdventureWorks2008R2 sample database.
USE AdventureWorks2008R2;
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:
From Object Explorer, right-click the database object and select Properties.
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