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?

Answer 1

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

Answer 2

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.

Answer 3

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