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

Question

Hey guys, so here's the deal.

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?

Thanks!

Answer 1

Use the USE command and repeat your commands

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

Answer 2

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 3

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

Answer 4

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 5

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.