Extended Properties Catalog Views - sys.extended_properties
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Returns a row for each extended property in the current database.
|Column name||Data type||Description|
|class||tinyint||Identifies 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
8 = User defined table type column
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_desc||nvarchar(60)||Description of the class on which the extended property exists. Can be one of the following:
|major_id||int||ID 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_id||int||Secondary 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.
|name||sysname||Property name, unique with class, major_id, and minor_id.|
|value||sql_variant||Value 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)