Using SQLCMD Mode to batch scripts

Bob Blackburn

SQLCMD is used when you need access to Windows System Commands and T-SQL in one script. You can change directories, create output files, use variables, and conditional logic. For this article, we will start simply and look at a quick way to execute multiple SQL scripts through one SQLCMD script.

Our example comes from having a set of stored procedures that access multiple databases on one server. Our Visual Studio project does not have references to all the database so they are maintained in a separate project. Every time we deploy the database or update the stored procedures we have to open and execute the create scripts manually. This is time consuming and error prone. SQLCMD is a perfect solution.

Let’s get started. Open a new Query Window and change it to SQLCMD Mode under the Query Menu.

We list out the sql files we want to execute. This is done with the :r command (Referenced File) just like an include statement. The quotes are only needed if you have spaces in the path or filename. We can also add a print statement to get some feedback on the overall script. Example:

:r "C:\Client\PrintHello.sql"
print convert(Varchar(30), GetDate(), 109) + '  -  PrintHello created'

Repeat this template for as many scripts as you need. You will almost always need a GO statement after each SQL statement. Now you have a shortcut that will run the latest scripts when you need them. When you open your script, you will have to change the execution to SQLCMD Mode.