Select Database names and extended properties in SQL Server

Question

I have three databases, starting with "MD_" that I have added in SQL Server 2012. Each of them has an extended property NAME = "DESCRIPTION"

What I like to have returned is a result set of the database names and the "DESCRIPTION" value.

Selecting the database names are easy enough but I could use some help with joining in the extended property.

BEGIN
    SELECT A.NAME
    FROM sys.databases A
    Where LEFT(A.NAME, 3) = 'MD_'
END

Results:

NAME   DESCRIPTION
MD_1   Initial
MD_2   Secondary
MD_3   Final

Any help would be greatly appreciated! Kind regards

consider: http://msdn.microsoft.com/en-us/library/ms186989(v=sql.90).aspx or similar stack question http://stackoverflow.com/questions/6714848/select-extended-property-from-sql-server-tables — xQbert, Feb 23 '13 at 14:37

Kenneth Fisher · Accepted Answer · 2013-02-24T06:56:04.993

7

The link in the comments helped me get here but sys.extended_properties is a per database view. So the properties for each database are contained in the database. This worked though.

CREATE TABLE #EP (DatabaseName varchar(255), PropertyName varchar(max), 
            PropertyValue varchar(max))

EXEC sp_msforeachdb 'INSERT INTO #EP SELECT ''?'' AS DatabaseName, 
            CAST(name AS varchar), CAST(Value AS varchar) 
        FROM [?].sys.extended_properties WHERE class=0'

And if you want all the databases and just properties where they exist.

SELECT db.Name, #EP.PropertyName, #EP.PropertyValue
FROM sys.databases db
LEFT OUTER JOIN #EP
    ON db.name = #EP.DatabaseName

edited Feb 24 '13 at 06:56

answered Feb 23 '13 at 15:28

Kenneth Fisher

3,692
19
21

Let me try to follow your logic on the first bit, as I have not used SQL Server in a while. First you're creating a temporary table? Then populating it with all extended_properties from every database? (I am getting an error here btw saying "Incorrect syntax near sp_msforeachdb") @Kenneth Fisher – Hank Feb 24 '13 at 00:12
@Hank, Sorry about the incorrect syntax. I didn't put EXEC in front of sp_msforeachdb. I even blogged about it recently and still forgot. Oh well. To the other part, yes, I am creating a temp table then sp_msforeachdb cycles through each database and I'm populating the temp table that way. sp_msforeachdb replaces the ? with the database name. Then once I have the temp table populated it's easy to query against sys.databases. – Kenneth Fisher Feb 24 '13 at 06:53

score 4 · Answer 2 · answered Aug 02 '13 at 15:22

4

Here is another option, does not use temporary table but returns multiple results...

EXEC sp_msforeachdb N'SELECT name, value 
FROM [?].sys.fn_listextendedproperty(default, default, default, default, default, default, default)'

answered Aug 02 '13 at 15:22

zam6ak

7,229
11
46
84

Select Database names and extended properties in SQL Server

2 Answers2