Skip to content

Latest commit

 

History

History
47 lines (40 loc) · 3.74 KB

extended-properties-catalog-views-sys-extended-properties.md

File metadata and controls

47 lines (40 loc) · 3.74 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.extended_properties (Transact-SQL)
Extended properties catalog views - sys.extended_properties
rwestMSFT
randolphwest
04/25/2024
sql
system-objects
reference
sys.extended_properties
sys.extended_properties_TSQL
extended_properties
extended_properties_TSQL
sys.extended_properties catalog view
TSQL
>=aps-pdw-2016 || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Extended properties catalog views - sys.extended_properties

[!INCLUDE SQL Server Azure SQL Database Synapse Analytics PDW Fabric SE Fabric DW]

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 values:

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 values:

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_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 is 1, minor_id is the column_id if column, else 0 if object.
If class is 2, minor_id is the parameter_id.
If class is 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.

Permissions

[!INCLUDE ssCatViewPerm] For more information, see Metadata Visibility Configuration.

Related content