Complex SQL Server Query between more than one database

Question

Hello,

I have Scenario and i want to convert it to a query. My Scenario: I have lot of dbs with same structure "Clientxxxx" i want to make loop for all these dbs to get data from one table exists in all these dbs called "EventLog" these event log recorded in this table for clients exists in another db called "Portal"

I want to get every client in "portal" db with his eventlogs from "EventLog" Table in the other dbs "Clientxxxx"

db:Client1          db:Client2            db:Client3
table:"EventLog"    table:"EventLog"      table:"EventLog"

each client has his db and his data in Portal db 

db:portal
table:Clients

query:
Client1 data 
his event logs 
client2 data
his event logs 
and so on 
........
........
........
........

I need some help please.

thanks

Answer 1

I would do the following:

Create a view in your Portal db that has this in it:

vw_AggregateClients:

SELECT 'Client1' as clientName, * from Client1.dbo.EventLog
UNION
SELECT 'Client2', * from Client2.dbo.EventLog
UNION
SELECT 'Client3', * from Client3.dbo.EventLog

And then query it like this:

SELECT * from vw_AggregateClients as ac
INNER JOIN Clients as c
ON ac.clientName = c.ClientName

If you the number of client dbs will be large or you don't know how many there will be then you will probably have to use dynamic sql. If you go that route give the article i linked to a good read.

Answer 2

Typically, I use the dynamic SQL approach, with a cursor to loop through all the databases, insert into a consolidated table variable, and then select out of the variable:

declare @return (dbname varchar(100),
                <<dataset spec for EventLog>>)

declare @db varchar(100)
declare @sql varchar(max)

declare recscan cursor for
select name from sys.databases
where database_id not in (1,2,3,4) --excludes system databases

open recscan
fetch next from recscan into @db

while @@fetch_status = 0
begin
    set @sql = 'select '''+@db+''',* from '+@db+'..eventlog'

    insert into @return
    exec(@sql)

    fetch next from recscan into @db
end

close recscan
deallocate recscan

select * from @return

Note that I create an extra field and put the database name as an explicit string value in the dynamic query so that I can break out where the data came from. I also use 3 part naming for the table object, but you could insert a dynamically constructed USE statement into your SQL variable.