SQL Server help: Find all comments in SQL server for all kind of objects

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:

SQL Server help

Sunday, April 25, 2021

Find all comments in SQL server for all kind of objects