动态查询以合并多个数据库 [英] Dynamic query to Union multiple databases

问题描述

说我在SQL Server 2008 R2中具有以下数据库

Say I have the following databases in SQL Server 2008 R2

db1, db2, db3, db4, db5......dbn

每个数据库都有一个表 A 包含列 C1，C2，C3

each database has a table A which contains the columns C1,C2,C3

我可以编写以下在两个数据库上选择语句以获取它们之间的数据：

I can write the following Select statement on two databases to get the data across them:

Select C1,C2,C3
FROM db1.dbo.A

UNION ALL

Select C1,C2,C3
FROM db2.dbo.A

但是，如果我在同一服务器上有50个数据库，我不想编写 UNION ALL

However if I have 50 databases on the same server I don't want to write a UNION ALL for each.

有人可以给我一个脚本来做到这一点吗？我可以修改脚本以自己排除系统数据库。

Can someone give me a script to do this? I can modify the script to exclude system databases myself.

谢谢

推荐答案

如果您知道数据库的确切数目：

If you know the exact number of DBs:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
UNION ALL
  SELECT C1,C2,C3
  FROM db' + CONVERT(VARCHAR(2), n) + '.dbo.A'
FROM 
(
  SELECT TOP (50) n = ROW_NUMBER() 
  OVER (ORDER BY [object_id])
  FROM sys.all_columns
) AS x;

SET @sql = STUFF(@sql, 1, 11, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

如果您不知道确切有50个，那可能会更好（它也允许您将不在线的排除在外）：

If you don't know that there are exactly 50, then this is probably better (it also allows you to exclude those that are not online):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
UNION ALL
  SELECT C1,C2,C3
  FROM ' + QUOTENAME(name) + '.dbo.A'
FROM sys.databases
WHERE state = 0
AND name LIKE N'db[0-9]%';

SET @sql = STUFF(@sql, 1, 11, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

这篇关于动态查询以合并多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！