This post details how to retrieve records using extended properties within your SQL Server database. The following SQL scripts demonstrate how to query various database objects for their associated extended properties.
Objects and Columns
SELECT
CASE
WHEN ob.parent_object_id > 0
THEN OBJECT_SCHEMA_NAME(ob.parent_object_id) + '.' + OBJECT_NAME(ob.parent_object_id) + '.' + ob.name
ELSE OBJECT_SCHEMA_NAME(ob.object_id) + '.' + ob.name
END + CASE
WHEN ep.minor_id > 0
THEN '.' + col.name
ELSE ''
END AS path
,'schema' + CASE
WHEN ob.parent_object_id > 0
THEN '/table'
ELSE ''
END + '/' + CASE
WHEN ob.type IN (
'TF'
,'FN'
,'IF'
,'FS'
,'FT'
)
THEN 'function'
WHEN ob.type IN (
'P'
,'PC'
,'RF'
,'X'
)
THEN 'procedure'
WHEN ob.type IN (
'U'
,'IT'
)
THEN 'table'
WHEN ob.type = 'SQ'
THEN 'queue'
ELSE LOWER(ob.type_desc)
END + CASE
WHEN col.column_id IS NULL
THEN ''
ELSE '/column'
END AS thing
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.objects ob ON ep.major_id = ob.OBJECT_ID
AND class = 1
LEFT OUTER JOIN sys.columns col ON ep.major_id = col.Object_id
AND class = 1
AND ep.minor_id = col.column_id
Indexes
SELECT --indexes
OBJECT_SCHEMA_NAME(ob.object_id) + '.' + OBJECT_NAME(ob.object_id) + '.' + ix.name
,'schema/' + LOWER(ob.type_desc) + '/index'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.objects ob ON ep.major_id = ob.OBJECT_ID
AND class = 7
INNER JOIN sys.indexes ix ON ep.major_id = ix.Object_id
AND class = 7
AND ep.minor_id = ix.index_id
Parameters
SELECT --Parameters
OBJECT_SCHEMA_NAME(ob.object_id) + '.' + OBJECT_NAME(ob.object_id) + '.' + par.name
,'schema/' + LOWER(ob.type_desc) + '/parameter'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.objects ob ON ep.major_id = ob.OBJECT_ID
AND class = 2
INNER JOIN sys.parameters par ON ep.major_id = par.Object_id
AND class = 2
AND ep.minor_id = par.parameter_id
Schemas
SELECT --schemas
sch.name
,'schema'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.schemas sch ON class = 3
AND ep.major_id = SCHEMA_ID
Database
SELECT DB_NAME()
,''
,ep.name
,value
FROM sys.extended_properties ep
WHERE class = 0
XML Schema Collections
SELECT SCHEMA_NAME(SCHEMA_ID) + '.' + XC.name
,'schema/xml_Schema_collection'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.xml_schema_collections xc ON class = 10
AND ep.major_id = xml_collection_id
Database Files
SELECT --Database Files
df.name
,'database_file'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.database_files df ON class = 22
AND ep.major_id = file_id
Data Spaces
SELECT --Data Spaces
ds.name
,'dataspace'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.data_spaces ds ON class = 20
AND ep.major_id = data_space_id
Users
SELECT --USER
dp.name
,'database_principal'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.database_principals dp ON class = 4
AND ep.major_id = dp.principal_id
Partition Function
SELECT --PARTITION FUNCTION
pf.name
,'partition_function'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.partition_functions pf ON class = 21
AND ep.major_id = pf.function_id
Remote Service Binding
SELECT --REMOTE SERVICE BINDING
rsb.name
,'remote service binding'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.remote_service_bindings rsb ON class = 18
AND ep.major_id = rsb.remote_service_binding_id
Route
SELECT --Route
rt.name
,'route'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.routes rt ON class = 19
AND ep.major_id = rt.route_id
Service
SELECT --Service
sv.name COLLATE DATABASE_DEFAULT
,'service'
,ep.name
,value
FROM sys.extended_properties ep
INNER JOIN sys.services sv ON class = 17
AND ep.major_id = sv.service_id
Contract
SELECT -- 'CONTRACT'
svc.name
,'service_contract'
,ep.name
,value
FROM sys.service_contracts svc
INNER JOIN sys.extended_properties ep ON class = 16
AND ep.major_id = svc.service_contract_id
Message Type
SELECT -- 'MESSAGE TYPE'
smt.name
,'message_type'
,ep.name
,value
FROM sys.service_message_types smt
INNER JOIN sys.extended_properties ep ON class = 15
AND ep.major_id = smt.message_type_id
Plan Guide
SELECT -- 'PLAN GUIDE'
pg.name
,'plan_guide'
,ep.name
,value
FROM sys.plan_guides pg
INNER JOIN sys.extended_properties ep ON class = 27
AND ep.major_id = pg.plan_guide_id
Assembly
SELECT -- 'assembly'
asy.name
,'assembly'
,ep.name
,value
FROM sys.assemblies asy
INNER JOIN sys.extended_properties ep ON class = 5
AND ep.major_id = asy.assembly_id
Certificates, Asymmetric Keys, and Symmetric Keys
Note: The following SQL queries for Certificates, Asymmetric Keys, and Symmetric Keys require the `class` values to be filled in for accurate results. You can find these values in the reference table below.
--UNION ALL
SELECT --'CERTIFICATE'
cer.name,'certificate', ep.name,value
FROM sys.certificates cer
INNER JOIN sys.extended_properties ep ON class=? AND ep.major_id=cer.certificate_id
UNION ALL --'ASYMMETRIC KEY'
SELECT amk.name,'asymmetric_key', ep.name,value
FROM sys.asymmetric_keys amk
INNER JOIN sys.extended_properties ep ON class=? AND ep.major_id=amk.asymmetric_key_id
SELECT --'SYMMETRIC KEY'
smk.name,'symmetric_key', ep.name,value
FROM sys.symmetric_keys smk
INNER JOIN sys.services sv ON class=? AND ep.major_id=smk.symmetric_key_id
Here's a table outlining the `class` values for different database items:
| 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 |
Additional Resources
For more detailed information, please refer to the following resources:
