SQL针对多个数据库的查询

Question

我正在尝试对同一服务器上的多个数据库运行查询。

如果数据库是在某个日期之后创建的，我需要根据数据库中第三个表的标准从数据库中的2个表中提取所有值。

我有一个查询来查找何时创建数据库:

SELECT *
FROM sys.databases
WHERE STATE = 0 --ignores offline databases
AND database_id > 4 --does not include master, model, msdb, tempdb
AND create_date > CONVERT(datetime, '2021-01-01')

我需要在每个数据库上运行的查询通常如下:

SELECT *
FROM Table1
INNER JOIN Table3 ON Table3.Column6=Table1.Column2
AND Table3.Column3='Value1'
AND Table3.Column4='Value2'
INNER JOIN Table2 ON Table3.Column6=Table2.Column2

我确实发现了这个问题，这实际上是我想要做什么，但是当我查看INFORMATION_SCHEMA时。TABLE_CATALOG列没有我要查询的表名。我想我可以试着把名字从系统里拉出来。因此，我尝试将其修改为:

DECLARE @cmd VARCHAR(max) = N''
SELECT @cmd += COALESCE(@cmd + ' UNION ALL ', '') + 'SELECT *
FROM [' + name + '].dbo.Table1
INNER JOIN [' + name + '].dbo.Table3 on Table3.Column6=Table1.Column2
AND Table3.Column3= ''Value1''
AND Table3.Column4=''Value2''
INNER JOIN [' + name + '].dbo.Table2 on Table3.Column6=Table2.Column2'
FROM sys.databases
WHERE STATE = 0
AND database_id>4
AND create_date>CONVERT(datetime,'2021-08-26')
SET @cmd = STUFF(@cmd, CHARINDEX('UNION ALL', @cmd), 10, '')
PRINT @cmd
EXEC(@cmd)

但是，当我运行它的日期早于2021-08-26(它捕获超过5个表)，我得到一个内存错误。我需要至少运行到4月初(最好到年初)，这将占用大约500张表。

在SQL中对多个数据库运行查询的推荐方法是什么?

Answer 1

我的建议是，不要试图构建一个庞大的UNION ALL动态SQL语句，而是构建一个#temp表来保存每个输出的结果，然后将相同的字符串发送到每个数据库要容易得多:

CREATE TABLE #hold(dbname sysname, Column1 {data type}, ...);
DECLARE @sql nvarchar(max), @exec nvarchar(1024);
SET @sql = N'SELECT DB_NAME(), *
FROM dbo.Table1
INNER JOIN dbo.Table3 
ON Table3.Column6 = Table1.Column2
AND Table3.Column3 = ''Value1''
AND Table3.Column4 = ''Value2''
INNER JOIN dbo.Table2 
ON Table3.Column6 = Table2.Column2;';
DECLARE @dbname sysname, @c cursor;
SET @c = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR
SELECT name FROM sys.databases
WHERE state = 0 -- ignores offline databases
AND database_id > 4 -- does not include master, model, msdb, tempdb
AND create_date > CONVERT(datetime, '20210101');
OPEN @c;
FETCH NEXT FROM @c INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec = QUOTENAME(@dbname) + N'.sys.sp_executesql';
INSERT #hold EXEC @exec @sql;
FETCH NEXT FROM @c INTO @dbname;
END;
SELECT * FROM #hold;

您还可以考虑投资sp_ineachdb，这是我编写的一个过程，用于帮助简化在每个数据库上下文中运行相同的命令。

• 使用sp_ineachdb
在SQL Server的每个数据库上下文中执行命令
• 在SQL Server的每个数据库上下文中执行命令-第2部分