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 don't need to do this in SSMS. You just need to create a `CMD` script.

**IF** you have a static set of databases to run on, then use the following:

```
@ECHO OFF

SET MyServer="(local)"
SET MyScript="c:\release.sql"

SQLCMD -S %MyServer% -E -i %MyScript% -d database1
SQLCMD -S %MyServer% -E -i %MyScript% -d database2
...
SQLCMD -S %MyServer% -E -i %MyScript% -d database20

```

**IF** you have a dynamic set of databases that can be queried for, then use the following:

```
@ECHO OFF

SET MyServer="(local)"
SET MyScript="c:\release.sql"
SET MyQuery="SET NOCOUNT ON; SELECT [Name] FROM [sys].[databases] sd WHERE sd.[name] LIKE N'%%[_]db' ORDER BY sd.[name];"

FOR /F %%B IN ('SQLCMD -h -1 -S %MyServer% -E -Q %MyQuery%') DO (
    REM remove the "echo" from the next line to run the scripts
    echo SQLCMD -S %MyServer% -E -i %MyScript% -d %%B -o results-%%B.txt
)

```

Using the `%%B` in the output filename will give you a different output file per database, as in:

> 
> results-database1\_db.txt  
> 
>  results-database2\_db.txt  
> 
>  ...
> 
> 
> 

---

Other notes:

* Use `(local)` instead of `localhost` when connecting to the local, default instance as it uses shared memory while `localhost` forces a TCP connection.
* If you are searching for an underscore in a `LIKE` statement, enclose it in square brackets else it is a single-character wild card (which still technically works sometimes, but could also match other characters): `[_]`
Thanks everyone who pitched in! The following seems like it might work (based on @srutzky's answer)

> 
> sqlcmd -S "localhost" -E -i "c:\release.sql" -d database1 -o results.txt
> 
> 
> 

The thing I am missing by using a cmd prompt instead of SSMS is that I don't think I can write cursor to loop through each database that ends with "\_db" and then execute against that... Here's the SQL I have but I just need to be able to put the link to the SQL file to execute.

[link](http://screencast.com/t/F7exp9xzV2j)

If I put the release script SQL into this file into the @text variable it doesn't work because it blows up on each GO statement I have in my release.sql file.

```
declare @text as nvarchar(max)
set @text = N'
-- GET AND RUN SCRIPT FROM DISK!
'

declare C_CURSOR CURSOR FOR 
    select  [Name] from sys.databases 
    where   name like '%_db' 
    order by name

declare @runtext as nvarchar(max)
declare @DB_Name as nvarchar(200)

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

    print @DB_Name
    set @runtext = 'select ''' + @DB_Name + ''' as DatabaseName
        use ' + @DB_Name + N'
    ' + @text

    exec sp_executesql  @runtext

    fetch next from C_CURSOR INTO @DB_Name
END

CLOSE C_CURSOR
DEALLOCATE C_CURSOR

```

Thanks again!