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

Here an exemple with TSQL (SQL Server 2017) :

DECLARE @dbname varchar(MAX) = '', @SQL varchar(MAX)
DECLARE db_cursor CURSOR STATIC FORWARD_ONLY FOR 
SELECT name FROM Sys.Databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @dbname  

WHILE @@Fetch_Status = 0
BEGIN
    SET @SQL = '
        USE [' + @DBName + '];
-- here code sql (no GO, be careful quote (double))
-- uncomment execute and comment print
    '
    PRINT @SQL
    -- EXECUTE (@SQL)
FETCH NEXT FROM db_cursor INTO @dbname 

END
CLOSE db_cursor
DEALLOCATE db_cursor

Answer 2

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 '+QUOTENAME(@dbname)+';

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

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

Answer 3

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

This technique can also be used to run the script against databases on other servers with the addition of a :CONNECT command. The connection reverts back to initial server/database after execution of the entire script:

:CONNECT SomeServer
USE DB4;
:r C:\SqlScript\YourLargeScript.sql
GO
:CONNECT SomeOtherServer
USE DB5;
:r C:\SqlScript\YourLargeScript.sql
GO

Important gotcha: Note GO batch separators are needed for :CONNECT to work as expected. I recommend including GO in the the invoking script like the above example but GO as the last line in the :r script file will also provide the desired results. Without GO in this example (or at the end of the script file), the script would run twice on SomeServer and not run against SomeOtherServer at all.

Answer 4

You can use the following script to run the same script on a set of databases. Just change the filter in the insert line.

declare @dbs table (
  dbName varchar(100),
  done bit default 0
)

insert @dbs select [name], 0 FROM master.dbo.sysdatabases WHERE [Name] like 'targets_%'

while (exists(select 1 from @dbs where done = 0))
begin
  declare @db varchar(100);
  select top 1 @db = dbName from @dbs where done = 0;
  exec ('
    use [' + @db + '];
    update table1 set
      col1 = '''',
      col2 = 1
    where id = ''45b6facb-510d-422f-a48c-687449f08821''
  ');
  print @db + ' updated!';
  update @dbs set done = 1 where dbName = @db;
end

If your SQL Server version does not support table variables, just use Temp Tables but don`t forget to drop them at the end of the script.

Answer 5

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.

select Id from ['+@dbName+'].schema.YourTableName

When you need to execute stored procedures on each server, the @script variable will have another content.

Answer 6

ApexSQL Propagate is 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:

When you load scripts and databases you should just click the “Execute” button and wait for the results:

Answer 7

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

Answer 8

this is the normal way of doing this :

suppose you want to do a select on database DBOther than it would be :

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

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