如何用SQL Server游标替代拼接语句跨多服务器多数据库查询？

现状与问题 #

我有多台服务器，上面的大量数据库架构完全一致。目前我通过STUFF函数拼接包含UNION的动态SQL语句存入变量，再用EXEC()执行，示例语句如下：

select * from [server1].[DBname1].dbo.[table] where [column] = @matchingtext
union 
select * from [server1].[DBname2].dbo.[table] where [column] = @matchingtext
union 
select * from [server1].[DBname3].dbo.[table] where [column] = @matchingtext
union 
select * from [server2].[DBname1].dbo.[table] where [column] = @matchingtext
union 
select * from [server2].[DBname2].dbo.[table] where [column] = @matchingtext
...etc.

我们有一张Pools表，包含[server]和[DBname]两个字段。但执行复杂查询时会触发资源限制，当前分批执行的方案速度太慢，希望改用游标实现查询，可采用两种结构：

• 外层循环遍历服务器名、内层循环遍历对应数据库名的嵌套游标
• 基于Pools表的单游标循环

以下是我当前的简化实现代码：

DECLARE @FOO NVARCHAR(MAX)
SELECT @FOO = 
    STUFF((
        SELECT  'union select name from ['+p.server+'].['+p.DB+'].[dbo].contacts where email = ''test@test.com'''
        FROM Pools p
        where 
         p.server in (
                        '01'
                        ,'02'
                        ,'03'
                        ,'04'
                        ,'05'
                        )
         and p.DB like '_m[_]%' 
         and p.DB not like '%test%' -- 排除测试库
         and p.DB not like '%demo%' -- 排除演示库
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '') -- 移除拼接后开头的"union"字符
+' order by 1 desc'
exec(@FOO)

游标实现方案 #

方案1：嵌套游标（外层遍历服务器，内层遍历数据库） #

这种方式可以按服务器分批执行查询，减少单批次的资源占用：

-- 声明变量存储服务器名、数据库名
DECLARE @ServerName NVARCHAR(128), @DBName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)

-- 外层游标：遍历目标服务器
DECLARE ServerCursor CURSOR FOR
SELECT DISTINCT [server]
FROM Pools
WHERE [server] IN ('01','02','03','04','05')

OPEN ServerCursor
FETCH NEXT FROM ServerCursor INTO @ServerName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 内层游标：遍历当前服务器下的目标数据库
    DECLARE DBCursor CURSOR FOR
    SELECT [DBname]
    FROM Pools
    WHERE [server] = @ServerName
      AND [DBname] LIKE '_m[_]%'
      AND [DBname] NOT LIKE '%test%'
      AND [DBname] NOT LIKE '%demo%'

    OPEN DBCursor
    FETCH NEXT FROM DBCursor INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 构造单库查询语句并执行
        SET @SQL = N'SELECT name FROM [' + @ServerName + N'].[' + @DBName + N'].dbo.contacts WHERE email = ''test@test.com'''
        EXEC sp_executesql @SQL

        FETCH NEXT FROM DBCursor INTO @DBName
    END

    CLOSE DBCursor
    DEALLOCATE DBCursor

    FETCH NEXT FROM ServerCursor INTO @ServerName
END

CLOSE ServerCursor
DEALLOCATE ServerCursor

方案2：基于Pools表的单游标循环 #

直接遍历Pools表中符合条件的所有服务器-数据库组合，逐个执行查询：

DECLARE @ServerName NVARCHAR(128), @DBName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)

-- 单游标：遍历所有符合条件的服务器-数据库组合
DECLARE PoolCursor CURSOR FOR
SELECT [server], [DBname]
FROM Pools
WHERE [server] IN ('01','02','03','04','05')
  AND [DBname] LIKE '_m[_]%'
  AND [DBname] NOT LIKE '%test%'
  AND [DBname] NOT LIKE '%demo%'

OPEN PoolCursor
FETCH NEXT FROM PoolCursor INTO @ServerName, @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'SELECT name FROM [' + @ServerName + N'].[' + @DBName + N'].dbo.contacts WHERE email = ''test@test.com'''
    EXEC sp_executesql @SQL

    FETCH NEXT FROM PoolCursor INTO @ServerName, @DBName
END

CLOSE PoolCursor
DEALLOCATE PoolCursor

补充说明 #

• 若需要将所有结果合并返回，可以声明一个临时表，在每次游标执行查询时将结果插入临时表，最后统一查询临时表输出
• 建议使用sp_executesql替代直接EXEC()，更安全且支持参数化（比如把email参数化，避免硬编码）

内容的提问来源于stack exchange，提问作者Tater