Is there a concept of an “include file” in SQL Server?

Question

I have a set of scripts that need to be run in a certain order. I would like to create a "master file" that lists all of the other files and their correct order. Basically like an include file from C++ or ASP/VBScript.

Answer 1

If you are using SQLCMD, you can use :r FileName to include a separate .sql file.

:r FileName

Parses additional T-SQL statements and SQLCMD commands from the file specified by FileName into the statement cache. FileName is read relative to the startup directory for Visual Studio. The file is read and executed after a batch terminator is encountered. You can issue multiple :r commands. The file can include any SQLCMD command, including the batch terminator that is defined in Tools, Options. You can use :r in a pre-deployment or post-deployment script to include other scripts.

Answer 2

I agree, a T-SQL preprocessor is badly needed. I developed my own one in C#, which took me like an hour. Besides control over order of execution of SQL scripts, it also allows me to have macros that look like scalar UDFs and are convenient to use, but perform as fast as inline UDFs.

Answer 3

Along with the SQLCMD mode in Management Studio, you can also use a batch file to use as a master file to arrange and call all your sql files by using the SQLCMD utility.

The SQLCMD mode is a simulation of the utility usage inside the Management Studio, so there's not big difference between the modes. Except that sometime I prefer to run batches (no more opening of M Studio...loading correct db...etc). I prefer to configure everything in the batch, including output files.

Answer 4

In the sense of the ancient C preprocessors definitely no

• T-SQL by itself has no such concept
• sqlcmd and sqlcmdmode of SSMS do not handle relative file paths
• sqlcmd and sqlcmdmode of SSMS do not support conditional including

---

If you have a set of files with given absolute paths, which you want to include in a unconditional order, you can use the command line tool sqlcmd or use SSMS in sqlcmdmode as mentioned by @mfredrickson and @Marian.

---

But if you want relative paths or conditional includes you need to use some wrapper calling sqlcmd (or one of its deprecated predecessors isql or osql).

Currently at my job in this situation, I'm reviewing and extending old vbs and hta code. I'm not completely happy, but it is a pragmatic decision. It is working and the end user is at home with the hta GUI and no additional software installation is required.

For a fresh design I would start thinking about using WPF and PowerShell to wrap calls of sqlcmd, but with our current customers I can't presume the presence of PowerShell V2.

In even former times we wrote simple cmd batches for this purpose, ome of them still in use.

I guess there are current tools, especially those targeting Workflows, which might be suitable here. I'm not familiar with those.

Answer 5

I like bernd_k's answer. Depending on how you named your scripts, like including a number, SQL Server PowerShell (SQLPS) could be used if you were running SQL Server 2008 or higher. Then even if you were using SQL 2005 you could utilize PowerShell and the SMO for 2005.

Side note: I believe SQLCMD will eventually be put on the depreciated list and is being replaced with SQLPS, PowerShell.

There are a few other options as well.

1. Setup each script as a step in a SQL Agent job. You can set the order they are executed.
2. Create an SSIS package to call each file. This can offer a little bit more control and offer a little bit more logging options. I like this method if the outcome of one script needs to be verified prior to the next one executing.