4

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?

4

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
3

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.

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.