Share via

Expand :r referenced files for SQLCMD into one file (SQLCmdScriptExpander)

  • Article

(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 –> https://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:

image

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.

-Jens

Comments

  • Anonymous
    March 13, 2011
    Awesome tool ! Exactly that I need Thanks a lot

  • Anonymous
    June 25, 2012
    Just found this awesome tool.  However, can you add support for specifying a text encoding for the output script? msdn.microsoft.com/.../system.text.encoding.aspx I have a special case where I am trying to concatenate many table lookup data scripts into a single script.  Unfortunately some of these lookup data have special characters in Windows-1252 encoding that does not have a UTF-8 equivalent.

  • Anonymous
    June 26, 2012
    Thanks for the feedback, updated the version to 0.2.0.0

  • Anonymous
    September 20, 2012
    The download link is broken.  Is there some other place where I can download the tool from?  I could really use it!

  • Anonymous
    November 05, 2014
    I have downloaded this but the version indicates it is still v0.1.0.0 but you say there is now v.0.2.0.0. Do I have an old version or was the version no not updated in the assemply. It would be great if the source could be shared on github for this!