Sql-server – Obtaining database information and values in extended properties

sql server

I need to obtain each DB (together with its information) from SQL Server and I am doing so by the below:

use master;
select *
from sys.databases
where database_id > 4 
order by name

I also need to obtain a particular value from extended properties. Currently I am doing the below FOR EACH DB manually to obtain this value:

use <db_name>
select value from sys.extended_properties
where class_desc='DATABASE' and name = 'Application Name'

However, I need to get the value outputted from the last query in a column for each of the DB displayed by the first query respectively. Can this be done?

Best Answer

You could use a cursor to loop over the databases and store the resultsets in a temporary table

CREATE TABLE #TEMP(Databasename nvarchar(500),
                   value sql_variant)

DECLARE @sql NVARCHAR(max);
DECLARE @dbname NVARCHAR(500);
DECLARE c cursor LOCAL FAST_FORWARD FOR SELECT NAME 
                                        FROM sys.databases 
                                        WHERE database_id > 4 
                                        ORDER BY NAME;
OPEN c 
FETCH NEXT FROM c INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
use '+QUOTENAME(@dbname)+'


SELECT '''+QUOTENAME(@dbname)+''' as databasename,
        value 
FROM sys.extended_properties
WHERE class_desc=''DATABASE'' and name = ''Application Name''';

INSERT INTO #TEMP
EXEC(@sql);

FETCH NEXT FROM c INTO @dbname;
END
CLOSE c;
DEALLOCATE c;

SELECT * FROM #TEMP;
DROP TABLE #TEMP;

Testing

USE my_test;  
GO  
EXEC sp_addextendedproperty   
@name = N'Application Name',   
@value = 'ValueHere';  


USE Test;  
GO  
EXEC sp_addextendedproperty   
@name = N'Application Name',   
@value = 'ValueHere';

Result

Databasename    value
[my_test]       ValueHere
[Test]          ValueHere
Related Question