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 THENOBJECT_SCHEMA_NAME(ob.parent_object_id) + '.'+ OBJECT_NAME(ob.parent_object_id) + '.' + ob.nameELSE OBJECT_SCHEMA_NAME(ob.object_id) + '.' + ob.nameEND + 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') = 1THEN 'column_level_' ELSE 'table_level_'ENDELSE '' 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.valueFROM sys.extended_properties epINNER JOIN sys.objects obON ep.major_id = ob.object_id AND ep.class = 1LEFT OUTER JOIN sys.columns colON ep.major_id = col.object_idAND ep.class = 1AND ep.minor_id = col.column_idAND ep.name = 'MS_Description'UNION ALLSELECT --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.valueFROM sys.extended_properties epINNER JOIN sys.indexes ixON ep.major_id = ix.object_idAND ep.class = 7AND ep.minor_id = ix.index_idWHERE ep.name = 'MS_Description'UNION ALLSELECT --Parameters OBJECT_SCHEMA_NAME(ob.object_id) + '.' + OBJECT_NAME(ob.object_id) + '.'+ par.name, 'schema/' + LOWER(ob.type_desc) + '/parameter', ep.valueFROM sys.extended_properties epINNER JOIN sys.objects obON ep.major_id = ob.object_id AND ep.class = 2INNER JOIN sys.parameters parON ep.major_id = par.object_idAND ep.class = 2AND ep.minor_id = par.parameter_idWHERE ep.name = 'MS_Description'UNION ALLSELECT --schemas sch.name, 'schema', ep.valueFROM sys.extended_properties epINNER JOIN sys.schemas schON ep.class = 3AND ep.name = 'MS_Description'AND ep.major_id = sch.schema_idUNION ALL --Database SELECT DB_NAME(), '', ep.valueFROM sys.extended_properties epWHERE ep.class = 0UNION ALL --XML Schema Collections SELECT SCHEMA_NAME(xc.schema_id) + '.' + xc.name,'schema/xml_Schema_collection', ep.valueFROM sys.extended_properties epINNER JOIN sys.xml_schema_collections xcON ep.class = 10AND ep.name = 'MS_Description'AND ep.major_id = xc.xml_collection_idUNION ALLSELECT --Database Files df.name, 'database_file', ep.valueFROM sys.extended_properties epINNER JOIN sys.database_files dfON ep.class = 22AND ep.name = 'MS_Description'AND ep.major_id = df.file_idUNION ALLSELECT --Data Spaces ds.name, 'dataspace', ep.valueFROM sys.extended_properties epINNER JOIN sys.data_spaces dsON ep.class = 20AND ep.name = 'MS_Description'AND ep.major_id = ds.data_space_idUNION ALLSELECT --USER dp.name, 'database_principal', ep.valueFROM sys.extended_properties epINNER JOIN sys.database_principals dpON ep.class = 4AND ep.name = 'MS_Description'AND ep.major_id = dp.principal_idUNION ALLSELECT --PARTITION FUNCTION pf.name, 'partition_function', ep.valueFROM sys.extended_properties epINNER JOIN sys.partition_functions pfON ep.class = 21AND ep.name = 'MS_Description'AND ep.major_id = pf.function_idUNION ALLSELECT --REMOTE SERVICE BINDING rsb.name, 'remote service binding', ep.valueFROM sys.extended_properties epINNER JOIN sys.remote_service_bindings rsbON ep.class = 18AND ep.name = 'MS_Description'AND ep.major_id = rsb.remote_service_binding_idUNION ALLSELECT --Route rt.name, 'route', ep.valueFROM sys.extended_properties epINNER JOIN sys.routes rtON ep.class = 19AND ep.name = 'MS_Description'AND ep.major_id = rt.route_idUNION ALLSELECT --Service sv.name COLLATE DATABASE_DEFAULT, 'service', ep.valueFROM sys.extended_properties epINNER JOIN sys.services svON ep.class = 17AND ep.name = 'MS_Description'AND ep.major_id = sv.service_idUNION ALLSELECT -- 'CONTRACT' svc.name, 'service_contract', ep.valueFROM sys.service_contracts svcINNER JOIN sys.extended_properties epON ep.class = 16AND ep.name = 'MS_Description'AND ep.major_id = svc.service_contract_idUNION ALLSELECT -- 'MESSAGE TYPE' smt.name, 'message_type', ep.valueFROM sys.service_message_types smtINNER JOIN sys.extended_properties epON ep.class = 15AND ep.name = 'MS_Description'AND ep.major_id = smt.message_type_idUNION ALLSELECT -- 'assembly' asy.name, 'assembly', ep.valueFROM sys.assemblies asyINNER JOIN sys.extended_properties epON ep.class = 5AND 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 ALLSELECT -- 'PLAN GUIDE' pg.name, 'plan_guide', ep.valueFROM sys.plan_guides pgINNER JOIN sys.extended_properties epON ep.class = 27AND ep.name = 'MS_Description'AND ep.major_id = pg.plan_guide_idORDER BY thing,path;

Get a detail list of all Database Object Extended Properties:

-- get extended properties of all objects in a DB SELECT --objects CASEWHEN 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.nameELSE OBJECT_SCHEMA_NAME(ob.object_id) + '.' + ob.nameEND AS Object_Name,ob.type_desc AS Object_Type,COALESCE(ep.value, '') AS Extended_Property --display the property if there FROM sys.objects obLEFT OUTER JOIN sys.extended_properties epON 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)+'.'+nameELSE 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(SELECTep.value,ob.object_id,ob.Parent_Object_id,ob.name,0,type_descFROM sys.objects obLEFT OUTER JOIN sys.extended_properties epON ep.major_id = ob.object_id --class= 1 means Object or column AND ep.class = 1AND ep.minor_id = 0AND ep.name='MS_Description'WHERE ob.is_ms_shipped = 0UNION ALL --AND now we add the columns SELECTep.value,0,col.Object_id,col.name,col.column_id,'COLUMN'FROM sys.columns colLEFT OUTER JOIN sys.extended_properties epON ep.major_id = col.object_id --1 means Object or column AND ep.class = 1AND ep.minor_id = column_idAND 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