SQL Server 2019跨多数据库批量执行同表查询的方案咨询

原代码失效原因 #

information_schema.tables默认仅返回当前连接数据库的表元数据，无法获取实例下其他数据库的表信息，因此拼接出来的语句只能覆盖单库，无法满足多库查询需求。

推荐方案：动态SQL批量拼接（无需循环，性能更优） #

该方案会自动遍历实例下所有在线业务库，拼接统一的COUNT查询语句，执行后直接返回每个数据库对应的统计结果：

-- 声明动态SQL存储变量
DECLARE @Query NVARCHAR(MAX) = ''

-- 遍历所有符合条件的数据库拼接查询逻辑
SELECT @Query = @Query +
'UNION ALL
SELECT ''' + name + ''' AS 数据库名, COUNT(*) AS 表行数
FROM [' + name + '].dbo.Aircraft
'
FROM sys.databases
WHERE database_id > 4 -- 过滤master、tempdb、model、msdb四个默认系统库
AND state = 0 -- 仅查询处于在线运行状态的数据库
-- 如有需要可在此处加额外过滤条件，比如 name IN ('DB1','DB2'...) 手动指定查询范围

-- 移除开头多余的UNION ALL关键字
SET @Query = STUFF(@Query, 1, 10, '')

-- 打印生成的SQL用于调试校验，确认无误后再执行后续逻辑
PRINT @Query

-- 执行拼接完成的动态查询
EXEC sp_executesql @Query

注意事项 #

• 确保当前执行账号对所有目标数据库有SELECT权限
• 如果Aircraft表所属Schema不是默认的dbo，请将代码中的dbo替换为实际Schema名称
• 建议先通过PRINT @Query的输出确认拼接逻辑正确，再执行最终查询
• 如果需要过滤特定数据库，可在sys.databases的WHERE条件中自定义规则

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