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?
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.
I think the easiest way would be to have a master batch file, and inside that batch file you could call sqlcmd to all of your script files (*.sql) sequentially.
Here's a good reference on how to do this: http://www.mssqltips.com/sqlservertip/1543/using-sqlcmd-to-execute-multiple-sql-server-scripts/
Since you put SSMS right in the title of your question, I'm going to assume that you want to be able to do this from there.
You can run SSMS in SQLCMD mode. This should give you much more flexibility. You can see a quick demo of it here or look for more information on Microsoft's website.
For simple command line functionality, I would personally go with a Powershell script. You can add in the Microsoft SQL cmdlets to allow you to connect to a SQL Server or you can roll your own with SMO or go with a third party solution, like the open source SQLPSX.
