如何查询多个链接的服务器? [英] How to query against multiple linked servers?

问题描述

链接一些SQL Server 2008服务器/实例后，我想对这些服务器进行更通用的查询.我知道我必须像这样指定查询的目的地:

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

但是，我将对多个链接服务器运行此查询.我也知道，此select语句恰好返回我需要的 SRV \ INSTANCE :

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

这一个，返回我要查询的所有 servers \ instances .

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

有什么想法吗?

谢谢.

推荐答案

您可以动态地动态创建SQL语句，然后运行该命令.在这种情况下，在带有帮助+ =运算符的@dml变量中，动态创建了整个命令

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

这篇关于如何查询多个链接的服务器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！