SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLCMD Mode; Run all SQL files in a directory

By Jason Selburg, (first published: 2015/09/14)

SQLCMD Mode gives you access to run command line executables from within SSMS and allows you to interact with those commands in your script. This can be quite powerful or just fun.

Example Use/Problem

Let’s say you need to run a long list of SQL scripts during a release or scheduled update. Rather than opening them all and running individually or creating one massive .sql file, I've come up with a way to programmatically make this happen.

Setup

First, you'll need to make sure SQLCMD mode is turned on for this Query window AND ALL FUTURE windows.

The sample files you need to run exist in the “C:\SQL_FilesToRun” folder and are attached below.

           

And these files contain

For starters, let’s make sure you understand some basic SQLCMD commands and rules.

  • :SETVAR variableName "variableValue" - sets a variable named variableName to the value of variableValue

  • !!<cmd> - two explanation points followed by <cmd>. Where <cmd> is ANY command line executable

  • :stdout - used for “standard output” essentially means the current SSMS window

  • :OUT - redirects the output of any following commands, both SQL and command line level to the output you define

  • $(variableName) - to reference a previously set variable, you use this syntax

  • :r - to run a specified file containing sql (which may also contain SQLCMD code)

For example, this code sets a variable and then uses it in a query:

The Process Explained

  1. These variables need to be set to the directory where the .sql files exist, the name of a temporary file, and the final txt file that will actually be executed.

  2. This line will clean up any file that might be left around. If you don’t do this and the file exists you’ll end up appending to it and running your files multiple times.

  3. Now we’ll redirect any future output to our working file that we defined above.

  4. Run a DIR command in bare format and ordered by filename.

The results of this are put into the working file defined in step 3. The reason to use the ordering is to help when the files are numbered or used with TFS task numbers to control that order. The result will give us a file containing this:

  1. Here’s where the real magic happens. We’re using a DOS level FOR .. DO loop, usebackq and @ECHO to append the SQLCMD :r to the front of each filename. Here is the code for that:

This results in a file of SQLCMD run commands

We now set the output back to the SSMS window and turn off row counts

Now you simply call the :r command and pass the filename from step 5 above.

This results in the files being run in order

Alternatively, you can just open Notepad and display the file.

Conclusion

In its simplest implementation, you can use this method to run any number of files in a particular order without having to open and run them manually. If you integrate this into your SDLC you can simplify the deployment/upgrade process.

As with any of my articles/scripts, this is intended to enable you to take this and run. Customize as you see fit. The sample files are attached for your use.

Feel free to contact me here with any questions or comments.

 

Resources:

run all sql files - sqlcmd.sql | SQL_FileNumber_.zip
Total article views: 6660 | Views in the last 30 days: 14
 
Related Articles
FORUM

sqlcmd -S

sqlcmd -S

FORUM

Migrating isql command to sqlcmd in SQL 2005 Issue

Format of the field returned in the output of isql & SQLCMD is different

FORUM

SQLCMD

Output

SCRIPT

Export Data in Text File Using SQLCMD in SQL SERVER 2005

This script export the data in text file using sqlcmd command.

FORUM

sqlcmd output

output only errors to log.txt

Tags
dos integration    
sqlcmd    
sqlcmd mode    
 
Contribute