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?

erikkallen · Accepted Answer · 2010-03-17 20:01:02Z

3

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

edited Mar 17, 2010 at 20:01

answered Mar 17, 2010 at 19:11

erikkallen

34.6k14 gold badges90 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

empz Over a year ago

Thanks, but it's not working. I mean, it's not switching to the databases, it's executing what I write on "-- Do whatever you want" several times on the same DB (the one that has the DatabaseNames table)

JohnFx Over a year ago

This won't work. PER MSDN entry on the EXEC statement "Changes in database context last only until the end of the EXECUTE statement."

StingyJack Over a year ago

Cursors are usually bad, but sometimes necessary (like in this example) +1 for that. @emzero + JohnFx - The reason this isn't working is because you will need to tack on your statement to the @stmt variable and exec() them as a batch.

Bratch Over a year ago

I've done something similar to this for processing a bunch of tables.

JohnFx Over a year ago

@StingyJack: I'm aware that the SQL to execute needs to be inside the EXEC statement, see my answer to the question below that uses that method.

|

devio · Accepted Answer · 2010-03-17 20:11:14Z

2

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.

answered Mar 17, 2010 at 20:11

devio

37.3k7 gold badges85 silver badges147 bronze badges

1 Comment

empz Over a year ago

Thanks! I didn't know about the SSMS Tool Pack. I'm going to try it, since it's the simplest way to do it without much effort.

mwigdahl · Accepted Answer · 2010-03-17 19:12:24Z

2

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

answered Mar 17, 2010 at 19:12

mwigdahl

16.6k7 gold badges52 silver badges64 bronze badges

2 Comments

Joe Over a year ago

This gives no control over which DBs are executed against.

mwigdahl Over a year ago

Sure it does! You just need to use the DB_NAME() builtin function in whatever query you are using. You can use it to exclude certain databases, or include them, depending on which is more suitable for the application.

JohnFx · Accepted Answer · 2010-03-18 14:14:17Z

2

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

edited Mar 18, 2010 at 14:14

answered Mar 17, 2010 at 19:34

JohnFx

35k19 gold badges108 silver badges169 bronze badges

2 Comments

erikkallen Over a year ago

Won't work. 1: There is no such statement as CREATE STORED PROCEDURE 2: CREATE PROCEDURE needs to be the first statement in a batch.

JohnFx Over a year ago

Fixed answer to include a method that will work with things that need to be in their own batch.

Community · Accepted Answer · 2017-05-23 12:14:33Z

0

Use the USE command and repeat your commands

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

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Mar 17, 2010 at 19:10

CResults

5,1051 gold badge24 silver badges29 bronze badges

2 Comments

empz Over a year ago

How can I use the USE command with a literal string from a result set? I mean I have the DB names on a varchar column.

CResults Over a year ago

@emzero, see my edit above but make sure you validate your input!

Joe B · Accepted Answer · 2015-10-15 18:11:22Z

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 '

Collectives™ on Stack Overflow

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

6 Answers 6

6 Comments

1 Comment

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

1 Comment

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related