(Updated version available in this blog post)

How can we expand files from a .sql file referenced with the :r operator ?

Ok, lets make a step back in the past and see how we used to reference files in .sql files and calling them via OSQL or ISQL. The answer is, we couldn't. We needed to use either one file with the whole statement OR call OSQL/ISQL for each files separately.

Now we have SQLCMD.EXE, the new command line utility for issuing SQL Server statements against a SQL Server was shipped with SQL Server 2005 supporting the new SQLCMD mode.

(Note: SQL Server Management Studio is also aware of that syntax, but it will have to be turned on by navigating to the main menu > Query > SQLCMD Mode. Be aware that this turn off Intellisense !)

SQLCMD mode includes some handy operators like referencing files, having the advantage rather to paste everything in one file, call one file which references another file(s) and so on.

The complete reference for that can be found here:

sqlcmd Utility –> http://msdn.microsoft.com/en-us/library/ms162773.aspx

A quick sample for that is:

Contents of File InitCustomerBase.sql:

:r .\StaticData\CustomerTypes.sql
:r .\StaticData\Cities.sql
:r .\CustomerData\LoadCustomerData.sql

Contents of LoadCustomerData.sql:

:r .\CustomerData\LoadCustomerFromSap.sql
:r .\CustomerData\LoadCustomerFromaTextFile.sql

This will be reflected in a folder structure like the following:


Once you load the InitCustomerData.sql file, the interpreter will notice the :r syntax and follow the referenced file (in that case CustomerTypes.sql) , execute the commands and return to the original file. If files are nested it will go through the files recursively. But the interpreter only does that for execution, no way to get him the script spit out for later execution.

Well, seems to be good structured and really good to maintain, right ? But when it comes to the point where you want to package the data and send it to somebody else who wants to inspect the files first and then execute it it it really hard to follow the different parts of the scripts / files. In addition, the executing person needs to have a command line interpreter (like SQLCMD) capable of understanding the syntax of :r to pickup the referenced files.

If any of these restrictions (or any other) apply to you, you might find this tool help called SQLCMDScriptExpander (sorry couldn’t find another handy name for it), written by me which can be downloaded here. it enables you to specify an input file for starting the crawl and an output file for spitting out the script nuggets. You will get one file containing all the statements from the referenced files (excluding the :r statement which were expanded). In addition you can configure it to be greedy with missing files, configure the recursion level to step through and if comments like “Starting content of LoadCustomerData.sql” are written out.

For your reference, the whole usage is:

/InputFile:<string>                 Input file where the references are grabbed from
/OutputfFile:<string>             Output file for the expanded content
/InsertComments:[true|false]   Will insert comments which file was read
                                            and expanded
/SkipMissingFiles:[true|false]   Will not abort execution if a file referenced
                                            is missing
/RecursiveLevel:[Integer]         Will not follow files in recursion of X levels
/Verbose                               Will enable verbose logging
/CheckForUpdates                  Will check for updates of the tool
/Help                                     Display this usage and ignore all other switches

Let me now if you found that helpful or if you have any feedback about that.