THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

How to Run a Series of T-SQL Scripts in a Specific Order

Another post in the handy-but-not-bleeding-edge category.

In the past few months I’ve seen a number of folks struggle with how to reliably/repeatedly execute a heap of T-SQL Script files, in order. One could certainly argue about why there’s the need for piles of scripts in text files, but that’s outside the scope of this post – today I want to focus on how to “get 'er done,” and save that philosophical discussion for another time.

Problem: Heap o’ Script Files

You receive a folder full of scripts from (who else) your ISV, together with a list of the files in the order they should be executed. This is obviously fraught with opportunities for error if one were to manually execute them each, one at a time.

Workaround: SQLCMD and :r

Thankfully, there’s a very easy way to encapsulate this with a SQLCMD “master” script to call all the individual files, predictably, in the correct order. The :r construct in SQLCMD is a file include, which means you can easily make a script that calls another text file, or many other text files, from disk.

First, copy your ordered file list and paste it into a query window. Be careful to avoid whitespace before and after each line of text:

another file.sql

Set the query to SQLCMD mode.

Next, use a regex find/replace to

  • Prepend each line with the text :r "yourFilePath\
  • End each line with a double-quotation mark, "
  • Add a GO batch terminator between each line

That is, find any line containing some text, using this regex:


Then Replace All using something like:

:r "c:\\some Folder\\\1"\nGO

The \1 within the replace expression will be populated with the original text from each line, which will, in effect, surround the original text with the quotation marks, file path, and GO. The extra backslashes are required to escape special characters including newline and backslash itself.

The resulting script should look like this:

:r "c:\some Folder\somefile.sql"
:r "c:\some Folder\another file.sql"
:r "c:\some Folder\thirdfile.sql"

Be careful with subtleties like whitespace around the file names – regex is tough to read and get correct, so it’s easy to make an error. It might take a couple of tries.

At the top of the script, again, you may also want this line:

:ON Error EXIT

This will cause the client to abort on any error from the scripts, instead of continuing.

Check, triple check, quadruple check your work, and you should have a meta-script that will call all the SQL files in order.

Cheers, and happy scripting!

Published Monday, September 12, 2011 10:36 AM by merrillaldrich
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



JeBrewer said:

Nice, this will save me a few headaches!

September 13, 2011 2:15 PM

Gary said:


Thanks a lot.  This will help.  I have created a bunch of small scripts to do a repetitive job.  They are small to modularize various functions.  And some use features of SSMS so they can't be in sprocs.

April 5, 2012 4:46 PM

JD said:

Very nice, just saved me a load of work, thanks

June 5, 2013 6:42 AM

mst said:

How does this approach differ logically from using the -i switch?

I have about 30 vendor scripts to run, to condition an empty db and get it ready for input. Many of these do contain one or more "GO" lines; from what I've read I *think* this will fail using :r

Is that correct-- and will an embedded "GO" cause the -i approach to fail as well?

It LOOKS like I can get it to stop when it detects errors using -b if I use -i

Are these all just different ways to skin the cat?

July 21, 2014 5:01 PM

Dev said:

How can I give relative path to Main script instead of hardcoding the path.

July 16, 2015 5:01 AM

Leave a Comment


This Blog


Privacy Statement