Microsoft SQL Server Management Studio running script from inside a script

Question

I want to be able to have a bunch of different create table scripts saved and stored seperately.

To build the entire database I would have a master .sql file that calls all the scripts in order so that all the foreign keys in the tables are created in the correct order.

I could then also make a script to delete the entire table structure by dropping them all in the reverse order.

Is there a way to do this?

Answer 1

Using the obscure r: SQLCMD command:

:r < filename >

Parses additional Transact-SQL statements and sqlcmd commands from the file specified by into the statement cache.

Say your create scripts are createT1.sql and createT2.sql then your master .sql file would be something like:

create database foo;
go

use foo;
go

:r createT1.sql
:r createT2.sql
go

This syntax works in SSMS as well, as long as you enable SQLCMD mode, see Editing SQLCMD Scripts with Query Editor. Also the dbutilsqlcmd library supports the :r extension if you want to embed this in your application.