How to query against multiple linked servers?

linked-server, multiserver, sql, sql-server-2008

Solution

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

Problem

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.

Original source