How to query against multiple linked servers?

Question

After linking some SQL Server 2008 Servers / instances, I would like to do a more generic query against these servers. I know that I must specify the destiny of the query like that:

select * 
from [SRV\INSTANCE].dbname.dbo.foo

But, I would run this query against more than one linked server. I know also that this select statement returns exactly the SRV\INSTANCE that I need:

select ss.name 
from sys.servers ss 
where ss.server_id > 0

This one, returns all servers\instances from where I want query against to.

At this scenario, all databases have the same structure, so I wanted to do something like this:

select * 
from [select ss.name from sys.servers ss where ss.server_id > 0].DBNAME.dbo.foo

Any ideas?

Thanks in advance.

Answer 1

You can dynamically create SQL statement on the fly and then run that command. In this scenario in @dml variable with help += operator the whole command dynamically is created

DECLARE @dml nvarchar(max) = N''
SELECT @dml += 'UNION ALL SELECT * FROM ' + QUOTENAME(ss.name) + 
               '.[DBNAME].[dbo].foo ' 
FROM sys.servers ss
WHERE ss.server_id > 0

SELECT @dml = STUFF(@dml, 1, 10, '')
EXEC sp_executesql @dml