Perform same SQL query on multiple databases

Question

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!

score 2 · Accepted Answer · edited Jun 20 '16 at 13:10

2

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;

edited Jun 20 '16 at 13:10

Andrew Birks

792
9
26

answered Jun 20 '16 at 11:40

Sean Pearce

1,150
5
10

Thank you for replying, I'm afraid I get the following error Incorrect syntax near '-'. I've had a google, and it might be to do with whitespace, but I've tried stripping out what I can and it's still not working. – Andrew Birks Jun 20 '16 at 12:25
I think this is because my table names have hyphens in them... Just trying to work this out now. – Andrew Birks Jun 20 '16 at 12:31

score 0 · Answer 2 · edited Jun 20 '16 at 11:14

0

You can use Registered Servers in SSMS. To run the same query on all databases.

In SSMS, go to View
Then Registered Servers
Right Click on Local Server Group then New Server Group
Type any name for the Group
Then the the Group name you created, right click and New Server Registration
And Server name Localhost (for example)
Then Right Click on Localhost and New Query. That will run all the same query on all databases

edited Jun 20 '16 at 11:14

marc_s

732,580
175
1,330
1,459

answered Jun 20 '16 at 11:10

mvisser

652
5
11

I have tried this, and it doesn't work for my scenario. – Andrew Birks Jun 20 '16 at 12:17

score 0 · Answer 3 · answered Jun 20 '16 at 11:31

Ignore below. Try this.

DECLARE @sql VARCHAR(8000)
DECLARE @string VARCHAR(100)= 'SMTPServer'
SET @sql = 'EXECUTE sp_MSForEachDB 
    ''USE ?; SELECT DB_NAME()AS DBName, 
        SettingName,
        SettingValue
    FROM HostSettings
    WHERE SettingName =''' + '''' + @string +'''''' + ''''

EXEC (@sql)

score 0 · Answer 4 · answered Jan 18 '22 at 17:59

declare @userdb_list table (name varchar(4000) not null);
-- fill the db list with custom subset
insert into @userdb_list
select name from sys.databases --can add where condition to filter db names

declare
@curr_userdb varchar(300),
@db_placeholder varchar(300),
@final_db_exec_query varchar(max),
@query varchar(max);
set @query = '' --  <add ur query here> 

set @db_placeholder = 'use {db}'; 
set @curr_userdb = (select min(name) from @userdb_list);

while @curr_userdb is not null
begin
set @final_db_exec_query = replace(@db_placeholder, '{db}', @curr_userdb +  '  ' + @query);
exec (@final_db_exec_query);
--print @final_db_exec_query
set @curr_userdb = (select min(name) from @userdb_list where name > @curr_userdb);
end
GO

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jan 18 '22 at 18:40

Perform same SQL query on multiple databases

4 Answers4

Related