在多个数据库上执行 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39336171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing SQL query on multiple databases
提问by Arthur Medeiros
I know my post has a very similar title to other ones in this forum, but I really couldn't find the answer I need.
我知道我的帖子与本论坛其他帖子的标题非常相似，但我真的找不到我需要的答案。
Here is my problem, I have a SQL Server running on my Windows Server. Inside my SQL Server, I have around 30 databases. All of them have the same tables, and the same stored procedures.
这是我的问题，我的 Windows Server 上运行着 SQL Server。在我的 SQL Server 中，我有大约 30 个数据库。它们都具有相同的表和相同的存储过程。
Now, here is the problem, I have this huge script that I need to run in all of these databases. I wish I could do it just once against all my databases.
现在，问题来了，我需要在所有这些数据库中运行这个庞大的脚本。我希望我可以对我的所有数据库只做一次。
I tried a couple things like go to "view" >> registered servers >> local server groups >> new server registration. But this solution is for many servers, not many databases.
我尝试了一些事情，比如去“查看”>>注册服务器>>本地服务器组>>新服务器注册。但是这个解决方案适用于很多服务器，而不是很多数据库。
I know I could do it by typing the database name, but the query is really huge, so it would take too long to run in all databases.
我知道我可以通过键入数据库名称来完成，但是查询确实很大，因此在所有数据库中运行都需要很长时间。
Does anybody have any idea if that is possible?
有没有人知道这是否可能？
回答by SteRO
ApexSQL Propagateis the tool which can help in this situation. It is used for executing single or multiple scripts on multiple databases, even multiple servers. What you should do is simply select that script, then select all databases against which you want to execute that script:
ApexSQL Propagate是可以在这种情况下提供帮助的工具。它用于在多个数据库甚至多个服务器上执行单个或多个脚本。您应该做的只是选择该脚本，然后选择要对其执行该脚本的所有数据库：
When you load scripts and databases you should just click the “Execute” button and wait for the results:
当您加载脚本和数据库时，您只需单击“执行”按钮并等待结果：
回答by Dan Guzman
Consider running the script in SQLCMD Modefrom SSMS (Query--SQLCMD Mode). This way, you can save the script to a file and run it in the context of each of the desired databases easily:
考虑从 SSMS（查询--SQLCMD 模式）以SQLCMD 模式运行脚本。这样，您可以将脚本保存到文件中，并轻松地在每个所需数据库的上下文中运行它：
USE DB1;
:r C:\SqlScript\YourLargeScript.sql
GO
USE DB2;
:r C:\SqlScript\YourLargeScript.sql
GO
USE DB3;
:r C:\SqlScript\YourLargeScript.sql
GO
回答by Sercan Timo?in
You can write script like this
你可以写这样的脚本
DECLARE CURSOR_ALLDB_NAMES CURSOR FOR
SELECT name
FROM Sys.Databases
WHERE name NOT IN('master', 'tempdb')
OPEN CURSOR_ALLDB_NAMES
FETCH CURSOR_ALLDB_NAMES INTO @DB_NAME
WHILE @@Fetch_Status = 0
BEGIN
EXEC('UPDATE '+ @DB_NAME + '..SameTableNameAllDb SET Status=1')
FETCH CURSOR_ALLDB_NAMESINTO INTO @DB_NAME
END
CLOSE CURSOR_ALLDB_NAMES
回答by Dan Mihalea
Depending on the requirement, you can do this:
根据要求，您可以执行以下操作：
declare @dbName nvarchar(100)
declare @script nvarchar(max)
declare @dbIndex bigint = 0
declare @dbCount bigint = (
select count(*) from
sys.databases
)
declare crs_databases cursor for
(
select
[name]
from
sys.databases
)
open crs_databases
fetch next from crs_databases into @dbName
while @@FETCH_STATUS = 0
begin
set @dbIndex = @dbIndex+1
set @script = concat(@script,
' select Id from ['+@dbName+']..YourTableName ',
case
when @dbIndex = @dbCount then ''
else 'union'
end)
fetch next from crs_databases into @dbName
end
select @script
close crs_databases
deallocate crs_databases
Please note that the double dotted notation assumes that the schema is dbo. Otherwise, you need to explicitly write down the schema.
请注意，双点符号假定架构是 dbo。否则，您需要明确写下架构。
select Id from ['+@dbName+'].schema.YourTableName
When you need to execute stored procedures on each server, the @scriptvariable will have another content.
当你需要在每台服务器上执行存储过程时，@script变量就会有另外的内容。
回答by GuidoG
this is the normal way of doing this :
这是执行此操作的正常方法：
suppose you want to do a select on database DBOther than it would be :
假设您想对数据库 DBOther 进行选择，而不是：
select * from DBOther..TableName
Also check if the table or view is on the dbo schema, if not you should add the schema also : Please notice I use only one dot now after the database name
还要检查表或视图是否在 dbo 架构上，如果不是，您还应该添加架构：请注意我现在只在数据库名称后使用一个点
select * from DBOther.dbo.ViewName
If any of the databases is on another server on another machine, than make sure the Database is in the Linked Server.
Then you can access the table or view on that database via:
如果任何数据库位于另一台机器上的另一台服务器上，请确保该数据库位于链接服务器中。
然后您可以通过以下方式访问该数据库上的表或视图：
SELECT * FROM [AnotherServerName].[DB].[dbo].[Table]
Here is another way that does not requires typing the database name :
这是另一种不需要输入数据库名称的方法：
use DB1
go
select * from table1
go
use DB2
go
select * from table1
go
Note that this will only work if the tables and fields are exact the same on each database
请注意，这仅在每个数据库上的表和字段完全相同时才有效
回答by Arthur Medeiros
Dan's answer solved my problem! Just one observation, I had to enable the SQLCMD Mode on Query tab in SQL Server Management. Also, the path of the file cannot have spaces. I appreciate your help!
丹的回答解决了我的问题！只是一个观察，我必须在 SQL Server 管理中的查询选项卡上启用 SQLCMD 模式。此外，文件路径不能有空格。我感谢您的帮助！
