Perform same SQL query on multiple databases

I’m trying to adapt an SQL query to check the value present in a certain field that is present in every database on my server.

There are 100 individual databases, and I would like to check a specific record of each one.

The answer is probably to use a command like the one below, but I’m having difficulty adapting it.

EXECUTE sp_MSForEachDB 
    'USE ?; SELECT DB_NAME()AS DBName, 
    COUNT(1)AS [Count] FROM CUSTOMERS'

I have had greater success with the link below;

https://stackoverflow.com/a/18462734/3461845

I need to be able to perform this query:

SELECT [SettingName],[SettingValue]  FROM [HostSettings] Where [SettingName] = 'SMTPServer'

And also pull back the name of the database for each row that is returned;

DBName | SettingName | SettingValue

Database1 | SMTPServer | smtp.gmail.com

Database2 | SMTPServer | smtp.gmail.com

Database3 | SMTPServer | smtp.yahoo.com

Any help is greatly appreciated.

Thanks!

Answer

DECLARE @T TABLE
([DbName] SYSNAME,
[SettingName] VARCHAR(255),
[SettingValue] VARCHAR(255));

INSERT INTO
@T
EXEC sp_MSForEachDB
'SELECT
    ''?'',
    [SettingName],
    [SettingValue]
FROM
    [?]..[HostSettings]
WHERE
    [SettingName] = ''SMTPServer''';

SELECT * FROM @T;

Advertisement

Answer