Find all comments in SQL server for all kind of objects

This script return record if we used comment using 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

USER

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

Certificate

Need to be change

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

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

Other References:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/extended-properties-catalog-views-sys-extended-properties?view=sql-server-ver15

Database documentation within a database