Script -> Query for multiple databases

Question

I have the following problem: I want to execute a query on multiple databases on my SQL Server. Every customer has a separate database. Those all have exactly the same table and their names are similar. So there is a database kde_01_Miller, then a kde_02_Mueller and so on ...

I want to execute a query in every one of those databases.

Here's what I have tried:

DECLARE @name VARCHAR(100) -- database name
DECLARE @dothis nvarchar(200)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name like 'kde_0%'
order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0
BEGIN  
       set @dothis = 'use [' + @name + ']'
       exec sp_executesql @dothis

       /* Start query */
       select description from dbo.basicdata
       /* End query */

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

The problem is that the query does not work properly. The use statement seems not to be working. I get a result for every database I have, but the result is always the same one, dependent on the database I'm currently doing a query for.

I've also tried the following and it worked: Instead of my while-loop I did this:

WHILE @@FETCH_STATUS = 0
BEGIN  
       set @dothis= 'select description from ' + QUOTENAME(@name) + '.dbo.basicdata'
       exec sp_executesql @dothis

       FETCH NEXT FROM db_cursor INTO @name  
END

But I don't like this way, because you need the quotename(@name) for every table.

How do I make the first example work?

Depending on the volume and your specific needs you could create a local registration group, add a registration for each customer DB into that group, the right click the group select new query. The resulting window will execute your query on all the DBs, providing a single result set along with source registration name. — Paul
– Paul, Commented May 22, 2017 at 12:44

NeedAnswers · Accepted Answer · 2014-08-01 08:41:09Z

1

That's not possible, since sp_executesql is executed as its own self-contained batch, that mean you did actually "use" other databases, but only in those batchs i mentioned earlier

I'll try to be more clear, this code of you is a batch, since there's no "GO" command inside (read my sql comments) :

DECLARE @name VARCHAR(100) -- database name
DECLARE @dothis nvarchar(200)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name like 'kde_0%'
order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0
BEGIN  
       set @dothis = 'use [' + @name + ']'
       -- this will create another batch and execute the @dothis
       -- it'll have nothing todo with your current executing batch, 
       -- which is calling the sp_executesql
       exec sp_executesql @dothis 

       /* Start query */
       select description from dbo.basicdata
       /* End query */

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

So, there's only one way left, write whatever you want to do with the database inside the @dothis :

declare @dothis nvarchar(max)
set @dothis = '
use [' + @name + ']
-- query start
Select description from dbo.basicdata
-- query end 
'
exec sp_executesql @dothis

edited Aug 1, 2014 at 8:41

answered Aug 1, 2014 at 8:24

NeedAnswers

1,4553 gold badges20 silver badges43 bronze badges

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

5 Comments

need_an_answer Over a year ago

Alright, now i understand why i didn't work. Thank you very much for your help!!

need_an_answer Over a year ago

Just one more question. How would you do this in my example: 'select description from dbo.basicdata where description = 'xyz'' I'm having problems with the quotes.

NeedAnswers Over a year ago

you will need to duplicate the quote : set @dothis = 'select * from x where a = ''bcx'' '

NeedAnswers Over a year ago

@need_an_answer if you're good with my answer, you mind marking it as answer ? Thanks

need_an_answer Over a year ago

sry, i'm new here, didn't know how. i guess, i did it right now :).

TJP · Accepted Answer · 2017-05-22 05:27:19Z

While this question has already been answered, I thought I'd provide a second answer I believe is better. Instead of using a Cursor you can generate dynamic SQL to query multiple databases.

DECLARE @sql NVARCHAR(Max);

SELECT @sql = COALESCE(@sql, '') + 'SELECT * FROM '  + [name] + '.sys.tables' + CHAR(13)
FROM sys.databases

PRINT @sql
EXEC sp_executesql @sql

The above SQL will Generate the following SQL.

SELECT * FROM master.sys.tables
SELECT * FROM tempdb.sys.tables
SELECT * FROM model.sys.tables
SELECT * FROM msdb.sys.tables
SELECT * FROM StackOverflow.sys.tables
SELECT * FROM AdventureWorks2012.sys.tables
SELECT * FROM AdventureWorksDW2012.sys.tables

As you see I was able to run a query against multiple databases. I could even UNION the data together if I'd like.

ChrisMM · Accepted Answer · 2020-02-25 13:47:38Z

though already answered in finding a solution for the same issue I wrote this query. it provides the data and it shows the source of the database which provides the answer. note the order by is only to show double values but slows down the result

declare @results table (
    name varchar(50),
       clientnr numeric(20) ,
       dbname_source varchar(20)

);

insert @results
exec sp_msforeachdb N'
use [?]

if left(''?'',3) = ''ons''        -- only execute the query against databases  that match the naming pattern in this example starting with ons  (use your own database names make sure to exclude systemdatabases


    --and ''?'' <> ''MIS_MTA''
begin

--select script  that to be executed over multiple databases

    select distinct
       c.name,
          c.identificationNo as numeric,
          d.table_catalog



    from [?].dbo.clients as c ,  [?].INFORMATION_SCHEMA.COLUMNS as d               
       where 1=1
       and isnumeric(c.identificationNo) = 1

end;
';

select * from @results
where
isnumeric(clientnr) = 1

order by 2
;

example result:

name    clientnr    dbname_source
TestclientA 9000    OnsDB
TestclientA 9000    OnsDB_Fixed
Storcken    9999    OnsDB_Fixed
Storcken    9999    OnsDB

Collectives™ on Stack Overflow

Script -> Query for multiple databases

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related