How to execute T-SQL for multiple 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 with different data.

I have one additional database in which there is one table in which the names of the specified databases are stored.

So what I need to do is iterate over the name of these databases and actually "switch" to each of them (use [dbname]) and execute the T-SQL script. Am i clear

Let me give an example (simplified from real):

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

Suppose DatabaseA, DatabaseB, and DatabaseC are real existing databases. So let me say that I need to create a new SP for these databases. I need some kind of script that iterates over these databases and runs a T-SQL script that I specify (maybe stored in a varchar variable or anywhere).

Any ideas?

Thanks!

Answer 1

I think this is not possible at all in TSQL, since, as others have pointed out,

• you first need to modify the database as a USE expression,

• followed by the instruction you want to execute, which, although not specified, is the DDL statement, which should be the first in the package.

• In addition, you cannot have GO in the string, which must be EXECuted.

I found a command line solution calling 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()" 

In the code example, the current Windows login is used to query all active databases from Master (replacement by using its own connection and query for databases) and a literal TSQL command is executed for each database found in this way. (line breaks for clarity only)

See sqlcmd command line options . You can also transfer its TSQL file.

If you want to enable manual database selection, see the SSMS Tools Pack .

Answer 2

The easiest way:

 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, it is obvious that this will not work for statements that should be the first statement in the package, for example CREATE PROCEDURE. You can use SQLCLR for this. Create and expand the class as follows:

 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 can do this with sp_MSforeachdb an unregistered stored procedure.

Answer 4

This method requires your SQL script to be executed on each database 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 

In addition, as noted by some. This approach is problematic if you want to run a command that should be the only one in the package.

Here is an alternative for this situation, but it requires more permissions than many database administrators may need, and you need to put your SQL in 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 + ' -ic:\test.sql' END CLOSE c DEALLOCATE c 

Answer 5

Use the USE and repeat your commands

Ps. See how to use USE with parameter here

Answer 6

I know this question is 5 years old, but I found it through Google, so others can as well.

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

Given that your database name table has already been 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 the counts in many databases that I recovered from several different sites with the same database schema installed.

Example: - Repeat the SQL commands in all site database archives.

 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 hardest part is single quotes