CODEWITHSUNDEEP
sql-server
Motaz
Motaz

Reputation: 310

How to get a list of all the databases with their extended properties?

I am working on SQL server 2008 I want to get all the databases in the server with a specific property related to the database. Say for example we have an extended property with key "x" for some databases in the server. i want the result to be

DatabaseName | x
Db1          | value1
Db2          | value2
Db3          | value3
Db4          | null

where Db4 doesn't have the extended property with key "x"

any help Thank you very much

Upvotes: 3

Views: 2691

Answers (1)

gofr1
gofr1

Reputation: 15987

You can use sys.extended_properties (info) and dynamic SQL to get all extended properties.

IF OBJECT_ID(N'tempdb..#extprops') IS NOT NULL DROP TABLE #extprops

CREATE TABLE #extprops (
    dbname nvarchar(max),
    class_desc sql_variant,
    [name] sql_variant,
    [value] sql_variant
)

DECLARE @sql nvarchar(max)

SELECT @sql = (
    SELECT 'USE '+QUOTENAME([name])+ ' INSERT INTO #extprops SELECT ''' +[name] + ''' as dbname, class_desc, [name], [value] FROM sys.extended_properties;' +CHAR(10)
    FROM sys.databases
    FOR XML PATH('')
)

PRINT @sql

EXEC sp_executesql @sql

SELECT *
FROM #extprops

Output:

dbname          class_desc          name                                    value
msdb            DATABASE            Microsoft_Management_Utility_Version    ___SQLVERSION___NEW___
AdventureWorks  DATABASE            MS_Description                          AdventureWorks 2014 Sample OLTP Database
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Check constraint [ProductLine]='r' OR [ProductLine]='m' OR [ProductLine]='t' OR [ProductLine]='s' OR [ProductLine]='R' OR [ProductLine]='M' OR [ProductLine]='T' OR [ProductLine]='S' OR [ProductLine] IS NULL
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Stores (including store contacts) that sell Adventure Works Cycles products to consumers.
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Manufacturing failure reasons lookup table.
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Primary key for ScrapReason records.
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Failure description.
...

If you print @sql you will see query like this:

USE [master] INSERT INTO #extprops SELECT 'master' as dbname, class_desc, [name], [value] FROM sys.extended_properties;
USE [tempdb] INSERT INTO #extprops SELECT 'tempdb' as dbname, class_desc, [name], [value] FROM sys.extended_properties;
USE [model] INSERT INTO #extprops SELECT 'model' as dbname, class_desc, [name], [value] FROM sys.extended_properties;

After that you can use any filters on #extprops table.

Upvotes: 2

Related Questions