Arthur Medeiros Arthur Medeiros - 3 months ago 21
SQL Question

Executing SQL query on multiple databases

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.

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?

Answer

Consider running the script in SQLCMD Mode from 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:

USE DB1;
:r C:\SqlScript\YourLargeScript.sql
GO
USE DB2;
:r C:\SqlScript\YourLargeScript.sql
GO
USE DB3;
:r C:\SqlScript\YourLargeScript.sql
GO