Extended Properties Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.extended_properties

sys.extended_properties

extended property in the current database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	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 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
class_desc	•	•	•	•	nvarchar(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
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.

TSQL

Sql 2005
SELECT [class], [class_desc], [major_id], [minor_id], [name], [value] FROM sys.extended_properties
Sql 2008
SELECT [class], [class_desc], [major_id], [minor_id], [name], [value] FROM sys.extended_properties
Sql 2008 R2
SELECT [class], [class_desc], [major_id], [minor_id], [name], [value] FROM sys.extended_properties
Sql 2012
SELECT [class], [class_desc], [major_id], [minor_id], [name], [value] FROM sys.extended_properties

Back to Top

---