Query Extended Properties from Multiple SQL Server Databases

By: Aaron Bertrand | Comments | Related: > Database Design

Problem

A popular way for people to document their databases, schemas, and objects is to use Extended Properties. This metadata is not extremely discoverable on its own, but plenty of queries out there (including previous tips) help you collect and display this information for your users or applications. Built-in functions and views like sys.fn_listextendedproperty and sys.extended_properties can be used within a single database, but what if you frequently need to query Extended Properties from a set of databases?

Solution

This came up as a question from a user who had a specific database-level Extended Property defined in each of the user databases on their system. They started by writing a query like this to find all database level extended properties where the extended property name is property:

SELECT db.database_id, db.name, eprop.value
FROM sys.databases AS db
INNER JOIN sys.extended_properties AS eprop
ON db.database_id = eprop.major_id
WHERE db.database_id > 4
  AND eprop.class = 0 -- database
  AND eprop.name = 'property';

This will never work, because each database has its own local copy of sys.extended_properties. Depending on where this code is run, you will either get one row or zero rows, either because that property doesn’t exist there, or because major_id typically refers to an object inside the database, not the database itself. When an extended property applies to a database-level entity (including the database itself), the major_id and minor_id are both 0. The join in this case makes little sense, and there really isn’t a way to fix it.

Create Sample Extended Properties

To follow along with the code it's helpful to have some sample data. To enter an extended property in SSMS, right click on a database and select Properties. On the Database Properties window go to Extended Events and add a new property. For the Name enter property and for the Value enter any value you want, this value is what will be returned in the queries below. You can do this for as many databases as you want. Also, you can name the extended property anything you want and add as many as you want, but we are keeping it simple.

Getting Extended Properties for All Databases

One way to collect the data is to dump all of the values from each database’s Extended Properties view into a #temp table, then join to the #temp table. Something like this for databases db1 and db2.

CREATE TABLE #eprops (dbid int, value nvarchar(4000));

INSERT #eprops (dbid, value) 
  SELECT DB_ID(N'db1'), 
         CONVERT(nvarchar(4000),value)
  FROM db1.sys.extended_properties
  WHERE class = 0
    AND name = 'property';

INSERT #eprops (dbid, value) 
  SELECT DB_ID(N'db2'), 
         CONVERT(nvarchar(4000),value)
  FROM db2.sys.extended_properties
  WHERE class = 0
    AND name = 'property';

-- repeat above for as many databases as you have
-- and replace highlight sections with database names

SELECT db.database_id, db.name, eprop.value
  FROM sys.databases AS db
  LEFT OUTER JOIN #eprops AS eprop
  ON db.database_id = eprop.dbid
  WHERE db.database_id > 4;

DROP TABLE #eprops;

Of course, that is no fun, because now you need to maintain this script, and keep the right set of databases in sync with the system (since databases get added, dropped, and renamed over time).

Dynamic SQL to Create Script for All Databases

You could use dynamic SQL to generate the list of insert statements you’d need, instead of hard-coding them:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'INSERT #eprops (dbid, value) SELECT ' 
  + CONVERT(varchar(11), database_id) + N', 
    CONVERT(nvarchar(4000),value)
  FROM '+ QUOTENAME(name) + '.sys.extended_properties
  WHERE class = 0
    AND name = N''property'';' 
  FROM sys.databases
  WHERE database_id > 4;

PRINT @sql;

Here is the code to capture all of the data and return the results for all databases. But this is still a really messy thing to include in all the queries you use to pull Extended Properties.

CREATE TABLE #eprops (dbid int, value nvarchar(4000));

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'INSERT #eprops (dbid, value) SELECT ' 
  + CONVERT(varchar(11), database_id) + N', 
    CONVERT(nvarchar(4000),value)
  FROM '+ QUOTENAME(name) + '.sys.extended_properties
  WHERE class = 0
    AND name = N''property'';' 
  FROM sys.databases
  WHERE database_id > 4;

--PRINT @sql;	
EXEC @sql;	

SELECT db.database_id, db.name, eprop.value
  FROM sys.databases AS db
  LEFT OUTER JOIN #eprops AS eprop
  ON db.database_id = eprop.dbid
  WHERE db.database_id > 4;

DROP TABLE #eprops;

Creating a View Against All Databases

A potentially more efficient approach in this scenario could be to create a view that unions all of the database’s Extended Properties together. You still have to use dynamic SQL somewhere to create the view, but then at least that gets abstracted away from you, and you don’t have to think about that logic in all of your queries.

Essentially, we want to create the following view in a central, permanent database:

CREATE VIEW dbo.my_extended_properties
AS
  SELECT dbid = 5, value
    FROM db1.sys.extended_properties
    WHERE class = 0
      AND name = N'property'
  UNION ALL
  SELECT dbid = 6, value
    FROM db2.sys.extended_properties
    WHERE class = 0
      AND name = N'property'  
    ...

Creating a View Dynamically for All Databases

You could create this view using a dynamic SQL approach similar to the one above.

In SQL Server 2017, you could take advantage of STRING_AGG() to build the list of statements in the UNION ALL:

DECLARE @view nvarchar(max) = N'CREATE VIEW dbo.my_extended_properties AS
  SELECT ';

SELECT @view += (SELECT STRING_AGG(N'dbid = ' 
  + CONVERT(varchar(11), database_id) + ', value FROM ' 
  + QUOTENAME(name) + '.sys.extended_properties
    WHERE class = 0 AND name = N''property''
  ',N' UNION ALL 
  SELECT ') FROM sys.databases WHERE database_id > 4
) + N';';

EXEC CentralDB.sys.sp_executesql @view;

In versions older than SQL Server 2017, you’ll have to use a slightly different approach, like FOR XML PATH:

DECLARE @view nvarchar(max) = N'CREATE VIEW dbo.my_extended_properties AS
  SELECT ';

SELECT @view += STUFF((SELECT ' 
  UNION ALL 
  SELECT DBID = ' + CONVERT(varchar(11), database_id) +', value FROM ' 
  + QUOTENAME(name) + '.sys.extended_properties 
    WHERE class = 0 AND name = N''property''' 
  FROM sys.databases WHERE database_id > 4
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,26,'') + N'
  ;';

EXEC CentralDB.sys.sp_executesql @view;

It’s messy, but now your queries can just join against the view (and of course you could include other properties in the view, I just aimed to keep the example simple):

SELECT db.database_id, db.name, eprop.value
FROM sys.databases AS db
INNER JOIN CentralDB.dbo.my_extended_properties AS eprop
ON db.database_id = eprop.dbid
WHERE db.database_id > 4;

Creating a DDL Trigger to Keep the View Current

Unfortunately, this view becomes a static entity. If SQL Server supported temporary views, that might be a runtime option, but then you’d still be brining that logic into the queries. So, you’re going to have to maintain this view as databases get added or removed, and I would typically use a server-level DDL trigger to stay on top of this.

For SQL Server 2017, you can use the STRING_AGG() method and also CREATE OR ALTER:

CREATE TRIGGER [RegeneratePropertiesView] 
ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
BEGIN
  DECLARE @view nvarchar(max) = N'CREATE OR ALTER VIEW dbo.my_extended_properties AS
    SELECT ';

  SELECT @view += (SELECT STRING_AGG(N'dbid = ' 
    + CONVERT(varchar(11), database_id) + ', value FROM ' 
    + QUOTENAME(name) + '.sys.extended_properties
      WHERE class = 0 AND name = N''property''
    ',N' UNION ALL 
    SELECT ') FROM sys.databases WHERE database_id > 4
  ) + N';';

  EXEC CentralDB.sys.sp_executesql @view;
END
GO

ENABLE TRIGGER [RegeneratePropertiesView] ON ALL SERVER;
GO

In versions older than SQL Server 2017, you’ll still have to use the FOR XML PATH approach, and you’ll need an extra command to drop the view first:

CREATE TRIGGER [RegeneratePropertiesView] 
ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
BEGIN
  EXEC CentralDB.sys.sp_executesql N'DROP VIEW dbo.my_extended_properties;';

  DECLARE @view nvarchar(max) = N'CREATE VIEW dbo.my_extended_properties AS
    SELECT ';

  SELECT @view += STUFF((SELECT ' 
    UNION ALL 
    SELECT DBID = ' + CONVERT(varchar(11), database_id) +', value FROM ' 
    + QUOTENAME(name) + '.sys.extended_properties 
    WHERE class = 0 AND name = N''property'''
  FROM sys.databases WHERE database_id > 4
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,26,'') + N'
  ;';

  EXEC CentralDB.sys.sp_executesql @view;
END
GO

ENABLE TRIGGER [RegeneratePropertiesView] ON ALL SERVER;
GO

You can use DROP VIEW IF EXISTS in SQL Server 2016 or later, but for earlier versions you may want to implement some error handling in case the view gets dropped by someone else or another process. This can become important because if the trigger fails, the database operation will also fail.

The only other scenario you may have to deal with is the case where a database’s state changes (e.g. to offline or suspect). I’ll show ways to deal with those scenarios in a future tip.

Summary

Extended Properties can be a powerful documentation mechanism, but can be complicated to use when you need to access multiple databases at once. This solution, while a bit clunky, can be a good alternative to keeping the mess in your queries and/or having to maintain an abstraction view manually.

Next Steps

Read on for related tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips