Executing SQL query on multiple databases

Question

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 1

You can use WHILE loop over all database names and inside loop execute query with EXECUTE. I think that statement SET @dbname = ... could be better, but this works too.

DECLARE @rn INT = 1, @dbname varchar(MAX) = '';
WHILE @dbname IS NOT NULL 
BEGIN
    SET @dbname = (SELECT name FROM (SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn 
        FROM sys.databases WHERE name NOT IN('master','tempdb')) t WHERE rn = @rn);

    IF @dbname <> '' AND @dbname IS NOT NULL
        EXECUTE ('use ['+@dbname+'];

            /* Your script code here */
            UPDATE some_table SET ... ;

        ');
    SET @rn = @rn + 1;
END;