Microsoft SQL Server Management Studio runs a script from within a script

Question

I want to have a bunch of different table creation scripts that are saved and saved separately.

To create the entire database, I will 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.

Then I could make a script to remove the entire table structure, discarding them all in reverse order.

Is there any way to do this?

Answer 1

Using the hidden r: SQLCMD command:

: r <filename>

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

Say your createT1.sql and createT2.sql creation scripts, then your main .sql file will look something like this:

 create database foo; go use foo; go :r createT1.sql :r createT2.sql go 

This syntax also works in SSMS if you enable SQLCMD mode, see Editing SQLCMD Scripts with the Query Editor . In addition, the dbutilsqlcmd library supports the extension :r if you want to embed it in your application.

Answer 2

I think the easiest way is to have a master batch file, and inside this batch file you can call sqlcmd for all of your script files (* .sql) sequentially.

Here is a good link on how to do this: http://www.mssqltips.com/sqlservertip/1543/using-sqlcmd-to-execute-multiple-sql-server-scripts/

Answer 3

Since you put SSMS right in the headline of your question, I'm going to suggest that you want it to be possible from there.

You can start SSMS in SQLCMD mode. This should give you much more flexibility. You can see a demo here or find more information on the Microsoft website .

For simple command line functionality, I personally would go with a Powershell script. You can add Microsoft SQL cmdlets to allow you to connect to SQL Server, or you can minimize your own SMO or work with a third-party solution, such as open source SQLPSX.