Running .sql file within a SQL query

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 1

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

Answer 2

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): [_]