动态查询以合并多个数据库 [英] 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屋!