How do I run several .sql scripts from one query?

Question

In SQL Server Management Studio, I want to execute a number of SQL scripts (saved queries) one after the other. This is simply to make it easier to run each. I could take each script and combine them all into one massive script and simply execute the lot, however I want it to all be separate so I can easily and simply run each bit by bit.

For example, something like this:

EXEC ('CreateTable1.sql')
EXEC ('CreateTable2.sql')
EXEC ('CreateSP1.sql')
EXEC ('CreateSP2.sql')
EXEC ('SetupTestData.sql')

And that way I can run each line individually and keep everything separate.

Answer 1

if you like you can run then from the command line using SQLCMD -i, and put the commands in to a batch script. then you can call this from SQL management studio using exec xp_cmdshell. Actually you can be brave and run a FOR command under an xp_cmdshell and do the lot in one line. Or perhaps just run xp_cmdshell on them one by one

Or you can take the approach redgate approach and read the files into variables and then call exec on them. This last approach is serious overkill if all you want to do is exec a few scripts in my opinion.