sys.extended_properties (Transact-SQL)
Collapse the table of content
Expand the table of content

sys.extended_properties (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a row for each extended property in the current database.

Column nameData typeDescription
classtinyintIdentifies the class of item on which the property exists. Can be one of the following:

0 = Database

1 = Object or column

2 = Parameter

3 = Schema

4 = Database principal

5 = Assembly

6 = Type

7 = Index

10 = XML schema collection

15 = Message type

16 = Service contract

17 = Service

18 = Remote service binding

19 = Route

20 = Dataspace (filegroup or partition scheme)

21 = Partition function

22 = Database file

27 = Plan guide
class_descnvarchar(60)Description of the class on which the extended property exists. Can be one of the following:

DATABASE

OBJECT_OR_COLUMN

PARAMETER

SCHEMA

DATABASE_PRINCIPAL

ASSEMBLY

TYPE

INDEX

XML_SCHEMA_COLLECTION

MESSAGE_TYPE

SERVICE_CONTRACT

SERVICE

REMOTE_SERVICE_BINDING

ROUTE

DATASPACE

PARTITION_FUNCTION

DATABASE_FILE

PLAN_GUIDE
major_idintID of the item on which the extended property exists, interpreted according to its class. For most items, this is the ID that applies to what the class represents. Interpretation for nonstandard major IDs is as follows:

If class is 0, major_id is always 0.

If class is 1, 2, or 7 major_id is object_id.
minor_idintSecondary ID of the item on which the extended property exists, interpreted according to its class. For most items this is 0; otherwise, the ID is as follows:

If class = 1, minor_id is the column_id if column, else 0 if object.

If class = 2, minor_id is the parameter_id.

If class 7 = minor_id is the index_id.
namesysnameProperty name, unique with class, major_id, and minor_id.
valuesql_variantValue of the extended property.

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

Catalog Views (Transact-SQL)
Extended Properties Catalog Views (Transact-SQL)
sys.fn_listextendedproperty (Transact-SQL)
sp_addextendedproperty (Transact-SQL)
sp_dropextendedproperty (Transact-SQL)
sp_updateextendedproperty (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft