Sql Server - Execute multiple scripts from a master script

I was fiddling about with Sql Server Management Studio today trying to come up with a way to setup a master .sql script that would call numerous other scripts. What I landed on was the sqlcmd command line utility. I'd have liked to have run this from the query editor itself, however the option that presented itself there was a stored proc named xp_cmdshell. I didn't feel like messing around with my security settings locally (it's apparently turned off by default for security concerns) and figured it would be an issue with the remote host as well, so I went ahead with sqlcmd instead.

Basically, I set up a simple batch script I called dbCreate.bat and put the following commands in it:

sqlcmd -E -dMyDatabaseName -iDBCreateAll.sql
pause

The -E argument tells the script to use your local machine's permissions. You'll need to change this for operations on a remote host. I'll provide a reference page in the links at the end of this article. The "-d" option allows you to specify the database context you want to work with. It's like including the "USE " option in your script itself. The -i argument tells sqlcmd what script you want to call. I didn't need a full path reference in this case because it uses relative paths and my batch file is in the same directory as the scripts I'm calling. The last piece here is the "pause" instruction. This is simply a command line instruction that awaits a user hitting a key before moving on. This would allow you to view the output from calling the batch file before the window disappears if you double-click the file rather than run it from an open command shell.

The last part to running a list of scripts from one master sql file is the contents of the master sql file itself.

--DBCreateAll.sql

:On Error Exit

PRINT 'CREATING DATABASE...'

:r .\DBCreate.sql

PRINT 'FINISHED CREATING DATABASE...'

PRINT 'LOADING DATA...'

:r .\DBDataLoad.sql

PRINT 'FINISHED LOADING DATA...'

PRINT 'FINISHED SETTING UP DATABASE'

It's pretty self explanatory, but basically your batch file calls the immediately following filename. The sqlcmd utility interprets the :r as an instruction to add the text from the referenced sql file to the statement cache for execution.

So that about covers it! Now you have a method to separate your scripts and still run them all in Sql Server.

Reference

• http://msdn.microsoft.com/en-us/library/ms162773.aspx
• http://msdn.microsoft.com/en-us/library/ms180944.aspx