Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Query Extended Properties from Multiple SQL Server Databases


By:   |   Last Updated: 2019-04-05   |   Comments   |   Related Tips: More > 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:



Last Updated: 2019-04-05


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools