Select from all dabatases across multiple servers

  • Hey Guys:

    I am pretty new to sql. I need help please. I have several client databases across 3 servers; datbase structures are same for all clients. I need to do a select across all datbases. How can I do that? Thanks in advance for your help.

     

     

     

  • You can go through Sp_addlinkedserver

    or use linked server in enterprise manager

    after linking you can use ....

    select Servername.databasename.tablename

  • Alok thanks for the response. We have two hundred dtabases. One way is to use

     

    EXEC sp_MSForEachDB 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')

      select top 10 ''[?]'',* from [?].dbo.individual1 o join [?].dbo.address1 c on o.ind_id =c.ind_id

    '

     

     But I am not sure how can I use the above with complex query that creates temp table for totals and does a select at the end.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply