Bsrhrki

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

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;

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.