Script to run all SQL files in a particular folder

Question

Can anyone help me write a script to run all of the SQL files in a particular folder?

I need to deploy a bunch of Stored Procedures and I want to deploy them all at once without having to manually run each one. We run Microsoft SQL Server 2005.

Answer 1

Windows command line processor support the for statement, so you can create a .cmd file with something like that:

for %%f in (*.sql) do osql -U<User> -P<Pass> -S<Server> -d<Database> -i%%f

This will process all .sql files of the current folder sequentially (you can use also sqlcmd as suggested in another answer).

Reference: Microsoft Windows XP - For

Also, you can specify the database directly in the script using at the beginning:

use DatabaseName
go

The go is important because some statements like CREATE VIEW or CREATE PROCEDURE needs to be at the beginning of a batch of SQL commands.

Answer 2

If you need to run them in a particular order then the best solution is just to write a bat file:

run fileA.sql
run fileB.sql
run fileZ.sql
run fileM.sql
...

Answer 3

I'm not 100% sure that sqlcmd can accept a wildcard, but try:

sqlcmd -U<Username> -P<Password> -S<ServerName> -d<DatabaseName> -i*.sql