Cfrgtkky

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?

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

I'd use dynamic T-SQL to combine the results into a single output.

DECLARE @cmd nvarchar(max);

SET @cmd = N'';



SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'

UNION ALL

' END + 'SELECT d.name

    , AppName = (

        SELECT ep.value 

        FROM ' + QUOTENAME(d.name) + N'.sys.extended_properties ep

        WHERE ep.class_desc = N''DATABASE'' 

            AND ep.name = N''Application Name''

      )

    , d.*

FROM sys.databases d

WHERE d.database_id = ' + CONVERT(nvarchar(11), d.database_id)

FROM sys.databases d

WHERE d.database_id > 4

    AND d.state_desc = N'ONLINE'

    AND d.user_access = 0

ORDER BY d.name;



SET @cmd = @cmd + N'



IF EXISTS (

        SELECT 1

        FROM sys.databases d

        WHERE d.user_access <> 0

)

BEGIN

    DECLARE @msg varchar(1000);

    SET @msg = ''One or more databases are in single user mode, and cannot be accessed by this session.'';

    RAISERROR (@msg, 14, 1);

END;

';



PRINT @cmd;

EXEC (@cmd);

The query above creates a T-SQL SELECT statement for each online, multi-user, non-system, database on the instance, and combines them using UNION ALL. It then executes all combined statements in one execution. If any database is not in multi-user mode, an error is generated to make it obvious that one or more databases are not included in the output.

Complementing other answers - you can use sp_ineachdb - written by Aaron Bertrand and is part of firstResponderKit from Brent Ozar.

DECLARE @cmd nvarchar(max) = N' 

  select  db_name(), value from sys.extended_properties

    where class_desc=''DATABASE'' and name = ''Application Name'';';



EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;