SQL Server Extended Properties: How to Query Metadata for All Database Object

Sunday, April 25, 2021

SQL Server Extended Properties: How to Query Metadata for All Database Object

Summary: A comprehensive guide and T-SQL script library for retrieving extended properties across SQL Server objects, including tables, columns, indexes, schemas, and Service Broker components.

Retrieving Extended Properties in SQL Server

Extended properties are a powerful way to store metadata (like descriptions, versions, or owner info) directly within your database. This post provides a complete library of scripts to query sys.extended_properties for every major database object.

1. Objects and Columns (Class 1)

This query pulls properties for tables, views, stored procedures, and specific 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]
  ,ep.name AS [Property Name]
  ,ep.value AS [Property 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;

2. Indexes (Class 7)


SELECT 
  OBJECT_SCHEMA_NAME(ob.object_id) + '.' + OBJECT_NAME(ob.object_id) + '.' + ix.name AS [Index Path],
  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;

3. Schemas (Class 3) and Database (Class 0)


-- Schemas
SELECT sch.name AS [Schema], ep.name, ep.value
FROM sys.extended_properties ep
INNER JOIN sys.schemas sch ON class = 3 AND ep.major_id = SCHEMA_ID;

-- Database Level
SELECT DB_NAME() AS [Database], ep.name, ep.value
FROM sys.extended_properties ep
WHERE class = 0;

4. Service Broker & Specialized Objects

Retrieving properties for Routes, Services, and Message Types.


-- Routes (Class 19)
SELECT rt.name, ep.name, ep.value
FROM sys.extended_properties ep
INNER JOIN sys.routes rt ON class = 19 AND ep.major_id = rt.route_id;

-- Services (Class 17)
SELECT sv.name COLLATE DATABASE_DEFAULT, ep.name, ep.value
FROM sys.extended_properties ep
INNER JOIN sys.services sv ON class = 17 AND ep.major_id = sv.service_id;

Quick Reference: Class ID Table

When writing custom queries against sys.extended_properties, use these class IDs:

Class ID	Object Type
0	Database
1	Object or Column
2	Parameter
3	Schema
7	Index
10	XML Schema Collection

SQL Server help

Sunday, April 25, 2021