遍历数据库名称列表，并对每个数据库中的同一个表运行查询

Question

我正在使用一个服务器，该服务器每月使用相同的表创建一个数据库。唯一更改的是数据库名称。我可以使用这个查询来创建一个数据库名称表。

Select name 
into DB_List
FROM master.sys.databases
where name Like '%DB%' and create_date > '2020-01-01'

哪个返回：

DB013120,
DB022920,
DB033120,
etc.

我需要获取不同的数据库名称，并将它们输入到另一个查询中，如下所示。

SELECT *
FROM [DB******].[dbo].[Table]

我知道我可以使用UNION，但问题是每次添加新数据库时都必须手动更新存储过程。当前在SQL SERVER 2016 上运行

Answer 1

您可以动态构建UNION。对于SQL Server 2017+：

DECLARE @sql  nvarchar(max),
@base nvarchar(max) = N'SELECT *
FROM $db$.dbo.[Table]';
SELECT @sql = STRING_AGG
(REPLACE(@base, N'$db$', QUOTENAME(name)),
' UNION ALL ')
FROM master.sys.databases
WHERE name LIKE N'%DB%' 
AND create_date > '20200101';
EXEC sys.sp_executesql @sql;

如果你使用的是旧版本(请提前说明(，你可以使用FOR XML PATH，它有点混乱，需要一些对空格敏感的清理：

DECLARE @sql  nvarchar(max),
@base nvarchar(max) = N'SELECT *
FROM $db$.dbo.[Table]';
SELECT @sql = STUFF((SELECT N' UNION ALL
' + REPLACE(@base, N'$db$', QUOTENAME(name))
FROM master.sys.databases
WHERE name LIKE N'%DB%' 
AND create_date > '20200101'
FOR XML PATH(''), TYPE).value(N'./text()[1]',N'nvarchar(max)'),1,10, '');
EXEC sys.sp_executesql @sql;