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