SQL Server-View database Extended Properties

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

A. The following example shows all extended properties set by the database itself.

 Use adventureworks2008r2; GO SELECT ObjType, objname, name, value From Fn_listextendedproperty (default  default, default default default default, default); GO

B. Displaying extended properties for all columns in a table

The following example shows the extended properties of a column contained in a table in a Production schema ScrapReason .

 Useadventureworks2008r2;GOSELECTObjType, objname, name, value fromFn_listextendedproperty (NULL,'Schema','Production','Table','ScrapReason','column',default);GO

C. Displaying extended properties for all tables in a schema

The following example shows the Sales extended properties of all tables contained in the schema.

 Use adventureworks2008r2; GO SELECT ObjType, objname, name, value  from Fn_listextendedproperty (NULL'schema'Sales  'table'defaultnullnull ); GO

using the Sys.extended_properties catalog view

You can display extended properties by querying the Sys.extended_properties catalog view. This view provides a simple way to return extended property values, which can also be used in conjunction with other catalog views to return additional data, such as the object name or object type associated with an extended property.

The following examples show how to use the Sys.extended_properties catalog view to display extended properties for various objects. Used in conjunction with other catalog views, you can return object names, such as column names in a table.

A. Displaying all extended properties in a database

The following example shows AdventureWorks2008R2 all the extended properties in the sample database.

	Copy Code
Use ADVENTUREWORKS2008R2; Goselect class, Class_desc, major_id, minor_id, name, Valuefrom sys.extended_properties; GO

B. Displaying extended properties for all indexes in a database

The following example shows AdventureWorks2008R2 all the extended properties of an index in the sample database.

	Copy Code
Use ADVENTUREWORKS2008R2; Goselect class, Class_desc, major_id, minor_id, Ep.name, s.name as [Index name], Valuefrom sys.extended_properties as EpIN NER JOIN sys.indexes as s on ep.major_id = s.object_id and ep.minor_id = S.index_idwhere class = 7; GO

C. Displaying extended properties for columns in a table

The following example shows AdventureWorks2008R2 extended properties for all columns in all tables in the sample database.

	Copy Code
Use ADVENTUREWORKS2008R2; Goselect major_id, minor_id, t.name as [Table name], c.name as [Column name], value as [Extended property]from Sys.extende D_properties as Epinner join Sys.tables as T on ep.major_id = t.object_id INNER join sys.columns as C on ep.major_id = C.O bject_id and ep.minor_id = C.column_idwhere class = 1; GO

You can display extended properties by querying the Sys.extended_properties catalog view. This view provides a simple way to return extended property values, which can also be used in conjunction with other catalog views to return additional data, such as the object name or object type associated with an extended property.

The following examples show how to use the Sys.extended_properties catalog view to display extended properties for various objects. Used in conjunction with other catalog views, you can return object names, such as column names in a table.

 

SQL Server-View database Extended Properties