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!

Andrew Birks · Accepted Answer · 2016-06-20 13:10:22Z

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, 2016 at 13:10

Andrew Birks

90010 silver badges26 bronze badges

answered Jun 20, 2016 at 11:40

Sean Pearce

1,1705 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Birks Over a year ago

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 Over a year ago

I think this is because my table names have hyphens in them... Just trying to work this out now.

marc_s · Accepted Answer · 2016-06-20 11:14:40Z

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, 2016 at 11:14

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 20, 2016 at 11:10

mvisser

6705 silver badges11 bronze badges

1 Comment

Andrew Birks Over a year ago

I have tried this, and it doesn't work for my scenario.

mvisser · Accepted Answer · 2016-06-20 11:31:34Z

0

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)

answered Jun 20, 2016 at 11:31

mvisser

6705 silver badges11 bronze badges

Comments

aquesh · Accepted Answer · 2022-01-18 17:59:13Z

0

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

answered Jan 18, 2022 at 17:59

aquesh

91 gold badge2 silver badges9 bronze badges

1 Comment

Community Over a year ago

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.

Collectives™ on Stack Overflow

Perform same SQL query on multiple databases

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related