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:
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!