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