CODEWITHSUNDEEP
sqlsql-serverdatabase
Knowledge2Share
Knowledge2Share

Reputation: 103

How to run single select statement across all the databases in the same server

I need to run a simple select statement across all the databases in the same server. I have around 30-40 databases. This table has same structure in all the databases. 

select * from table1 where condition

Can you please let me know how to get the records from all databases?? BTW i'm using SQL Server

Upvotes: 0

Views: 372

Answers (4)

Ashish Kumar
Ashish Kumar

Reputation: 157

Try this: add all of your databases as mentioned in the SQL below:

SELECT a, b, c FROM Database1.Schema1.Table1
UNION 
SELECT a, b, c FROM Database2.Schema2.Table2
...

Upvotes: 0

M.Ali
M.Ali

Reputation: 69514

I would use a cursor for this, sp_msforeachdB has a bad reputation for skipping databases.

A solution with a cursor would look something like this..... 

DECLARE @DB_Name SYSNAME;
DECLARE @Sql NVARCHAR(MAX);

DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR 
SELECT Name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb','model','msdb')

OPEN Cur 
FETCH NEXT FROM Cur INTO @DB_Name

WHILE (@@FETCH_STATUS = 0)
BEGIN

   SET @Sql =  N' select * from '+QUOTENAME(@DB_Name)
             + N'.[sechmaName].table1 where condition'

    Exec sp_executesql @Sql
  FETCH NEXT FROM Cur INTO @DB_Name

END

CLOSE Cur;
DEALLOCATE Cur;

Upvotes: 1

benjamin moskovits
benjamin moskovits

Reputation: 5458

Exec sp_msforeachdB 'select top 5 cola from dbo.tablea'

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

If you want the results in a single result set you can leverage the system views to generate dynamic sql. This also negates the need to use any looping.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'select * from ' + db.name + '.table1 where condition union all '
from sys.databases db
--optionally add a where clause here to filter out certain databases
order by db.name

set @SQL = left(@SQL, len(@SQL) - 10)

select @SQL 
--uncomment the dynamic sql call when satisfied the sql is correct.
--exec sp_executesql @SQL

Upvotes: 1

Related Questions