CODEWITHSUNDEEP
sqlsql-serverautomation
chilleo
chilleo

Reputation: 546

Microsoft SQL Server Management Studio running script from inside a script

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?

Upvotes: 8

Views: 13728

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294307

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.

Upvotes: 14

Tom H
Tom H

Reputation: 47464

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.

Upvotes: 2

user596075
user596075

Reputation:

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/

Upvotes: 3

Related Questions