Get Database Extended Properties

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Description

3 queries to produce lists of Extended Properties for a database

Either a full list, objects only or objects and columns

Useful for database documentation

Code

Get a detail list of all Database Extended Properties

--get all extended properties for a database

SELECT --we start off by listing objects AND columns that are documented

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_desc LIKE '%constraint%'

THEN CASE WHEN OBJECTPROPERTYEX(ob.object_id, 'CnstIsColumn') = 1

THEN 'column_level_' ELSE 'table_level_'END

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.value

FROM sys.extended_properties ep

INNER JOIN sys.objects ob

ON ep.major_id = ob.object_id AND ep.class = 1

LEFT OUTER JOIN sys.columns col

ON ep.major_id = col.object_id

AND ep.class = 1

AND ep.minor_id = col.column_id

AND ep.name = 'MS_Description'

UNION ALL

SELECT --indexes

OBJECT_SCHEMA_NAME(ix.object_id) + '.' + OBJECT_NAME(ix.object_id) + '.'

+ ix.name,

'schema/'

+ CASE WHEN OBJECTPROPERTYEX(ix.object_id, 'BaseType') = 'U' THEN 'table'

ELSE 'view'

END + '/index', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.indexes ix

ON ep.major_id = ix.object_id

AND ep.class = 7

AND ep.minor_id = ix.index_id

WHERE ep.name = 'MS_Description'

UNION ALL

SELECT --Parameters

OBJECT_SCHEMA_NAME(ob.object_id) + '.' + OBJECT_NAME(ob.object_id) + '.'

+ par.name, 'schema/' + LOWER(ob.type_desc) + '/parameter', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.objects ob

ON ep.major_id = ob.object_id AND ep.class = 2

INNER JOIN sys.parameters par

ON ep.major_id = par.object_id

AND ep.class = 2

AND ep.minor_id = par.parameter_id

WHERE ep.name = 'MS_Description'

UNION ALL

SELECT --schemas

sch.name, 'schema', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.schemas sch

ON ep.class = 3

AND ep.name = 'MS_Description'

AND ep.major_id = sch.schema_id

UNION ALL --Database

SELECT DB_NAME(), '', ep.value

FROM sys.extended_properties ep

WHERE ep.class = 0

UNION ALL --XML Schema Collections

SELECT SCHEMA_NAME(xc.schema_id) + '.' + xc.name,

'schema/xml_Schema_collection', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.xml_schema_collections xc

ON ep.class = 10

AND ep.name = 'MS_Description'

AND ep.major_id = xc.xml_collection_id

UNION ALL

SELECT --Database Files

df.name, 'database_file', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.database_files df

ON ep.class = 22

AND ep.name = 'MS_Description'

AND ep.major_id = df.file_id

UNION ALL

SELECT --Data Spaces

ds.name, 'dataspace', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.data_spaces ds

ON ep.class = 20

AND ep.name = 'MS_Description'

AND ep.major_id = ds.data_space_id

UNION ALL

SELECT --USER

dp.name, 'database_principal', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.database_principals dp

ON ep.class = 4

AND ep.name = 'MS_Description'

AND ep.major_id = dp.principal_id

UNION ALL

SELECT --PARTITION FUNCTION

pf.name, 'partition_function', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.partition_functions pf

ON ep.class = 21

AND ep.name = 'MS_Description'

AND ep.major_id = pf.function_id

UNION ALL

SELECT --REMOTE SERVICE BINDING

rsb.name, 'remote service binding', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.remote_service_bindings rsb

ON ep.class = 18

AND ep.name = 'MS_Description'

AND ep.major_id = rsb.remote_service_binding_id

UNION ALL

SELECT --Route

rt.name, 'route', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.routes rt

ON ep.class = 19

AND ep.name = 'MS_Description'

AND ep.major_id = rt.route_id

UNION ALL

SELECT --Service

sv.name COLLATE DATABASE_DEFAULT, 'service', ep.value

FROM sys.extended_properties ep

INNER JOIN sys.services sv

ON ep.class = 17

AND ep.name = 'MS_Description'

AND ep.major_id = sv.service_id

UNION ALL

SELECT -- 'CONTRACT'

svc.name, 'service_contract', ep.value

FROM sys.service_contracts svc

INNER JOIN sys.extended_properties ep

ON ep.class = 16

AND ep.name = 'MS_Description'

AND ep.major_id = svc.service_contract_id

UNION ALL

SELECT -- 'MESSAGE TYPE'

smt.name, 'message_type', ep.value

FROM sys.service_message_types smt

INNER JOIN sys.extended_properties ep

ON ep.class = 15

AND ep.name = 'MS_Description'

AND ep.major_id = smt.message_type_id

UNION ALL

SELECT -- 'assembly'

asy.name, 'assembly', ep.value

FROM sys.assemblies asy

INNER JOIN sys.extended_properties ep

ON ep.class = 5

AND ep.name = 'MS_Description'

AND ep.major_id = asy.assembly_id

/*UNION ALL SELECT --'CERTIFICATE'

cer.name,'certificate', value from sys.certificates cer

INNER JOIN sys.extended_properties ep ON class=?

AND ep.name = 'MS_Description' AND ep.major_id=cer.certificate_id

UNION ALL SELECT --'ASYMMETRIC KEY'

amk.name,'asymmetric_key', value SELECT * from sys.asymmetric_keys amk

INNER JOIN sys.extended_properties ep ON class=?

AND ep.name = 'MS_Description' AND ep.major_id=amk.asymmetric_key_id

SELECT --'SYMMETRIC KEY'

smk.name,'symmetric_key', value from sys.symmetric_keys smk

INNER JOIN sys.services sv ON class=?

AND ep.name = 'MS_Description' AND ep.major_id=smk.symmetric_key_id */

UNION ALL

SELECT -- 'PLAN GUIDE'

pg.name, 'plan_guide', ep.value

FROM sys.plan_guides pg

INNER JOIN sys.extended_properties ep

ON ep.class = 27

AND ep.name = 'MS_Description'

AND ep.major_id = pg.plan_guide_id

ORDER BY thing,path;

Get a detail list of all Database Object Extended Properties:

-- get extended properties of all objects in a DB

SELECT --objects

CASE

WHEN ob.parent_object_id > 0 --if it is a child object

THEN OBJECT_SCHEMA_NAME(ob.parent_object_id) --add the parent

+ '.' + OBJECT_NAME(ob.parent_object_id) + '.' + ob.name

ELSE OBJECT_SCHEMA_NAME(ob.object_id) + '.' + ob.name

END AS Object_Name

,ob.type_desc AS Object_Type

,COALESCE(ep.value, '') AS Extended_Property --display the property if there

FROM sys.objects ob

LEFT OUTER JOIN sys.extended_properties ep

ON ep.major_id = ob.object_id --class of 1 means Object or column

AND ep.class = 1 AND ep.minor_id = 0 AND ep.name='MS_Description'

WHERE ob.is_ms_shipped = 0 -- leave out all the system stuff

ORDER BY [Object_name] -- and order by the object name

Get a detail list of all Database Object and Column Extended Properties

-- get extended properties of all objects and columns in a DB

-- ordered by object name and column_id so the order matches table creation script

SELECT --objects AND columns

CASE WHEN parent_object_id > 0 --if it is a child object

THEN OBJECT_SCHEMA_NAME(parent_object_id)--add the parent

+ '.'+OBJECT_NAME(parent_object_id)+'.'+name

ELSE OBJECT_SCHEMA_NAME(object_id) + '.' + name END AS Object_Name

,type_desc As Object_Type

,COALESCE(value, '') AS Extended_Property --display the property if there

FROM

(

SELECT

ep.value

,ob.object_id

,ob.Parent_Object_id

,ob.name

,0

,type_desc

FROM sys.objects ob

LEFT OUTER JOIN sys.extended_properties ep

ON ep.major_id = ob.object_id --class= 1 means Object or column

AND ep.class = 1

AND ep.minor_id = 0

AND ep.name='MS_Description'

WHERE ob.is_ms_shipped = 0

UNION ALL --AND now we add the columns

SELECT

ep.value

,0

,col.Object_id

,col.name

,col.column_id

,'COLUMN'

FROM sys.columns col

LEFT OUTER JOIN sys.extended_properties ep

ON ep.major_id = col.object_id --1 means Object or column

AND ep.class = 1

AND ep.minor_id = column_id

AND ep.name='MS_Description'

WHERE ObjectPropertyEx(col.OBJECT_ID,'IsMSShipped') = 0

) AS ObjsAndCols(value, Object_id, Parent_Object_id,name, column_id, type_desc)

ORDER BY [Object_Name], column_id