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

Accepted Answer

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
```

Select Database names and extended properties in SQL Server

Solution

Problem

Related problems