CODEWITHSUNDEEP
sqlsql-servert-sql
TheBoubou
TheBoubou

Reputation: 19933

Execute a query at once on several databases

I have an SQL Server database with several database (CustomerA, CustomerB, ....), all with the same structure.

Is it possible to execute at once a query on all databases (CustomerA, CustomerB, ....) ?

Below for one server :

Use CustomerA
GO
select * from Loggin.NLog where messages like '%error%'

The result I'd like is :

CustomerA
Row with error
Row with error
CustomerB
Row with error

Upvotes: 1

Views: 73

Answers (2)

Thom A
Thom A

Reputation: 95978

You could consider using the (undocumented) sp sp_MSforeachdb:

USE master;
GO

EXEC sp_MSforeachdb 'USE ?
IF (''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')) BEGIN
    PRINT ''Running query on ?'';
    SELECT ''?'' AS DatabaseName, * FROM Loggin.NLog WHERE messages LIKE ''%error%'';    
END';

Upvotes: 2

Oscar
Oscar

Reputation: 13990

If you have all the servers registered as linked servers in your ddbb, yes, you can. 

select * from LinkedServer1.Loggin.NLog where messages like '%error%'
UNION ALL 
select * from LinkedServer2.Loggin.NLog where messages like '%error%'

Take a look here about registering linked servers

Upvotes: 2

Related Questions