Question: I have a SQL script I need to run on about 20 different databases.

I basically just need to be able to run some SQL, then have it load and run a file from the disk, do more SQL, run that same script again, etc.

I was hoping to make a SQL script that would basically look something like this:

```
use database1
go
exec c:\release.sql
go

use database2
go
exec c:\release.sql
go

use database3
go
exec c:\release.sql
go

```

--etc....

I've looked online a bunch and found a way to do something similar in a batch file with sqlcmd but it isn't working and I don't see how to switch databases that way, either.

Thanks a ton!

Ben

Answer: 
You can switch management studio to sqlcmd mode (query menu) and then run a script with `:r script.sql` 

To do this on a dynamically generated list of databases you have to do some sqlcmd trickery:

* set output to file
* generate the command to execute
* set output to stdout
* execute the file
* delete the temp file

I assume in this example that the file script.sql exists in `c:\temp`. Note that the `GO` statements are important in the script or the sqlcmd parser will get confused.

```
:OUT $(TEMP)\db.sql
declare @script nvarchar(max)
select @script = isnull(@script, '') 
               + 'use ' + name + char(13) + char(10) 
               + ':r c:\temp\script.sql' + char(13) + char(10) 
  from sys.databases
 where name like N'%[_]db'
print @script

GO

:OUT stdout
:r $(TEMP)\db.sql

GO

!!del $(TEMP)\db.sql /s /q

```
I ended up combining 2 things. I made a SQL script that creates a cursor to find the databases and then prints a list of commands for a CMD prompt. I then run that in the command prompt. Below is what we output with our sql script and then save as a .bat file that we run. It's working great!

That script is essentially created with the following SQL script:

```
/*** GET DATABASES IN THE CURSOR QUERY BELOW! */
declare C_CURSOR CURSOR FOR 
        select  [Name] from sys.databases 
        where   name like '%_db' 
        order by name

/* THIS IS WHERE THE CURSOR STARTS*/
declare @DB_Name as nvarchar(200)

OPEN C_CURSOR
fetch next from C_CURSOR INTO @DB_Name
WHILE(@@FETCH_STATUS = 0)
BEGIN

    print 'SQLCMD -S "localhost" -E -i "C:\release.sql" -d ' +  @DB_Name + ' -o ' +  @DB_Name + '_results.txt'

    fetch next from C_CURSOR INTO @DB_Name
END

CLOSE C_CURSOR
DEALLOCATE C_CURSOR

```

That outputs the following which we then run in a .bat file

```
SQLCMD -S "localhost" -E -i "C:\release.sql" -d database1 -o database1_results.txt
SQLCMD -S "localhost" -E -i "C:\release.sql" -d database2 -o database2_results.txt
SQLCMD -S "localhost" -E -i "C:\release.sql" -d database3 -o database3_results.txt

```

Thanks everyone!