Retrieve the same tables and columns result set from 40 database at once

[Mel]

Hi SQL expert,

I have about 40 different databases and they have the same exact table and column structure. How can I retrieve all of them at once using the same select statement?

For example:

USE [Database1]
GO

SELECT a.Column1, b.Column3,
FROM table1 a
JOIN table2 b
ON a.key1 = b.key1

The above query will only give me the result set for 1 database only. I can get the different result set from the different database by changing the USE header but what I am trying to do is to run my select statement and get all the result set from all 40 databases?

[robert_volk]

Use 3-part names (include the DB name) and then UNION ALL:

SELECT a1.Column1, b1.Column3,
FROM DB1..table1 a1
JOIN DB1..table2 b1
ON a1.key1 = b1.key1
UNION ALL
SELECT a2.Column1, b2.Column3,
FROM DB2..table1 a2
JOIN DB2..table2 b2
ON a2.key1 = b2.key1
UNION ALL
SELECT a3.Column1, b3.Column3,
FROM DB3..table1 a3
JOIN DB3..table2 b3
ON a3.key1 = b3.key1
UNION ALL
...

[harishgg1]

hi

please see sp_MSforeachdb
which allows you to run the same command on all databases

hope it helps

DECLARE @command varchar(1000)

SELECT @command = 'USE ? SELECT a.Column1, b.Column3,
FROM table1 a JOIN table2 b ON a.key1 = b.key1'

EXEC sp_MSforeachdb @command

[Mel]

This is nice harishgg1 but I am getting invalid object name TABLE1 and TABLES2 error but I can see all the result set

[harishgg1]

Hi

This error comes if the table does not exist
In the database

Maybe it's looking at system databases
Master
model
tempdb
Reportdb

To avoid it
Try

EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
SELECT ''?''
Select statement here
END

[Mel]

Got the same result and also same error, harishgg1

[harishgg1]

Please use this suggestion from Robert volk given earlier

Use 3-part names (include the DB name)
For tables

[Mel]

I don't see this will work since the total databases are 40 instead 3. Am I correct, robert?

[harishgg1]

What I meant was use full 4 part name for tables

In sp_msforeachdb

[Mel]

Hm..

[robert_volk]

Yes, you are correct, I wasn't clear when I put the ellipsis (...) in the query, I meant to say you continue that pattern for the remaining 37 databases, with UNION ALL between each SELECT.

[harishgg1]

Mel

Another way is to generate the script and copy paste it
in New SQL Query window and run it

Script generation -- Script

script to generate script

SELECT 'use ' + NAME + ' go' 
       + '  SELECT a.Column1, b.Column3, FROM table1 a JOIN table2 b ON a.key1 = b.key1 go' 
FROM   sys.databases 
WHERE  NAME NOT IN ( 'master', 'tempdb', 'model', 'msdb' )

[harishgg1]

hi mel

please google search for

all possible scenarios show up
please see which one fixs your problem

hope it helps

[yosiasz]

from https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

DECLARE @command varchar(1000) 

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') USE ? 
SELECT a.Column1, b.Column3 FROM table1 a JOIN table2 b ON a.key1 = b.key1'  

EXEC sp_MSforeachdb @command 

[ScottPletcher]

IF OBJECT_ID('tempdb.dbo.#results') IS NOT NULL
    DROP TABLE #results;
SELECT TOP (0) DB_NAME() AS db_name, column1, column3, GETDATE() AS when_run
INTO #results
FROM dbo.table1 a
INNER JOIN dbo.table2 b

EXEC dbo.sp_MSforeachdb '
IF ''?'' NOT IN (''Database1'',''...your_list_of_db_names_here...'')
    RETURN;
USE [?];
PRINT ''?'';
INSERT INTO #results
SELECT ''?'', a.Column1, b.Column3, GETDATE()
FROM dbo.table1 a
INNER JOIN dbo.table2 b
ON a.key1 = b.key1
'

SELECT *
FROM #results
ORDER BY db_name