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"
go
print convert(Varchar(30), GetDate(), 109) + ' -
PrintHello created'
GO
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.
