How to execute T-SQL for several databases whose names are stored in a table?

Question

I have several databases (SqlServer 2005) on the same server with the same schema but different data.

I have one extra database which has one table storing the names of the mentioned databases.

So what I need to do is to iterate over those databases name and actually "switch" to each one (use [dbname]) and execute a T-SQL script. Am I clear?

Let me give you an example (simplified from the real one):

CREATE TABLE DatabaseNames
(
   Id   int,
   Name varchar(50)
)
INSERT INTO DatabaseNames SELECT 'DatabaseA'
INSERT INTO DatabaseNames SELECT 'DatabaseB'
INSERT INTO DatabaseNames SELECT 'DatabaseC'

Assume that DatabaseA, DatabaseB and DatabaseC are real existing databases. So let's say I need to create a new SP on those DBs. I need some script that loops over those databases and executes the T-SQL script I specify (maybe stored on a varchar variable or wherever).

Any ideas?

Answer 1

The simplest way is this:

DECLARE @stmt nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT 'USE [' + Name + ']' FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO @stmt
    IF @@fetch_status <> 0 BREAK
    SET @stmt = @stmt + ' ' + @what_you_want_to_do
    EXEC(@stmt)
END
CLOSE c
DEALLOCATE c

However, obviously it will not work for statements that need to be the first statement in a batch, like CREATE PROCEDURE. For that you can use SQLCLR. Create and deploy a class like this:

public class StoredProcedures {
    [SqlProcedure(Name="exec_in_db")]
    public static void ExecInDb(string dbname, string sql) {
        using (SqlConnection conn = new SqlConnection("context connection=true")) {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand()) {
                cmd.CommandText = "USE [" + dbname + "]";
                cmd.ExecuteNonQuery();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Then you can do

DECLARE @db_name nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT Name FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO @@db_name
    IF @@fetch_status <> 0 BREAK
    EXEC exec_in_db @db_name, @what_you_want_to_do
END
CLOSE c
DEALLOCATE c

Answer 2

You should be able to do this with the sp_MSforeachdb undocumented stored procedure.

Answer 3

This method requires you to put your SQL script to be executed on each DB in a variable, but should work.

DECLARE @SQLcmd varchar(MAX)
SET @SQLcmd ='Your SQL Commands here'

DECLARE @dbName nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT dbName FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO @dbName 
    IF @@fetch_status <> 0 BREAK
    EXEC('USE [' + @dbName + '] ' + @SQLcmd )
END
CLOSE c

Also, as some have pointed out. This approach is problematic if you want to run a command that needs to be the only thing in a batch.

Here is an alternative for that situation, but it requires more permissions than many DBA's might want you to have and requires you to put your SQL into a separate text file.

DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT dbName FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO @dbName 
    IF @@fetch_status <> 0 BREAK
     exec master.dbo.xp_cmdshell 'osql -E -S '+ @@SERVERNAME + ' -d ' + @dbName + '  -i c:\test.sql'
END
CLOSE c
DEALLOCATE c

Answer 4

I guess this will generally not be possible in TSQL, since, as others pointed out,

• you first need as USE statement to change the database,

• followed by the statement you want to execute, which is, although not specified, a DDL statement which must be first in a batch.

• Moreover, you cannot have a GO in a string to be EXECuted.

I found a command-line solution invoking sqlcmd:

for /f "usebackq" %i in 
    (`sqlcmd -h -1 -Q 
     "set nocount on select name from master..sysdatabases where status=16"`)
    do
        sqlcmd -d %i -Q "print db_name()"

Sample code uses current Windows login to query all active databases from Master (replace with your own connection and query for databases), and executes a literal TSQL command on each database thus found. (line breaks for clarity only)

Have a look at the command-line parameters of sqlcmd. You can pass it a TSQL file as well.

If you want to allow manual selection of databases, have a look at SSMS Tools Pack.

Answer 5

Use the USE command and repeat your commands

Ps. Have a look at how to use USE with a parameter here

Answer 6

I know this question is 5 years old, but I found this via Google, so others may as well.

I recommend sp_msforeachdb system stored procedure. You do not need to create any other stored procedures or cursors.

Given your table of database names is already created:

EXECUTE sp_msforeachdb '
USE ? 

IF DB_NAME() 
    IN( SELECT name DatabaseNames )
BEGIN

    SELECT 
              ''?'' as 'Database Name'  
            , COUNT(*)
    FROM
            MyTableName
    ;

END
'

I do this to summarize counts in many databases I have restored from several different sites with the same installed database schema.

Example: -- Repeat the execution of SQL Commands across all site archived databases.

PRINT       'Database Name'
+ ',' +     'Site Name'
+ ',' +     'Site Code'
+ ',' +     '# Users'
+ ',' +     '# Seats'
+ ',' +     '# Rooms'
...  and so on...
+ ',' +     '# of days worked'
;


EXECUTE sp_msforeachdb 'USE ?

IF DB_NAME() 
    IN( SELECT name FROM sys.databases  WHERE name LIKE ''Site_Archive_%'' )
BEGIN

DECLARE  @SiteName      As Varchar(100);
DECLARE  @SiteCode      As Varchar(8);

DECLARE  @NumUsers  As Int
DECLARE  @NumSeats  As Int
DECLARE  @NumRooms  As Int
... and so on ...

SELECT  @SiteName = OfficeBuildingName FROM  Office
...

SELECT @NumUsers = COUNT(*)  FROM   NetworkUsers
...

PRINT       ''?'' 
+ '','' +     @SiteName 
+ '','' +     @SiteCode
+ '','' +     str(@NumUsers)
...
+ '','' +     str(@NumDaysWorked) ;
END
'

The trickiest part are the single quotes '