Birksy89 Birksy89 - 5 months ago 7
SQL Question

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;

http://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;