Run all SQL files in a directory

Question

I have a number of .sql files which I have to run in order to apply changes made by other developers on an SQL Server 2005 database. The files are named according to the following pattern:

0001 - abc.sql
0002 - abcef.sql
0003 - abc.sql
...

Is there a way to run all of them in one go?

Answer 1

What I know you can use the osql or sqlcmd commands to execute multiple sql files. The drawback is that you will have to create a script for both the commands.

Using SQLCMD to Execute Multiple SQL Server Scripts

OSQL (This is for sql server 2000)

http://msdn.microsoft.com/en-us/library/aa213087(v=SQL.80).aspx

Answer 2

Try the following:-

EDIT :- Make sure you have SQLCMD enabled by clicking on the Query > SQLCMD mode option in the management studio.

A) Suppose you have four .sql files (script1.sql,script2.sql,script3.sql,script4.sql ) in say in a folder c:\scripts.

B) Create a main script file (Main.sql) with the following:-

:r c:\Scripts\script1.sql

:r c:\Scripts\script2.sql

:r c:\Scripts\script3.sql

:r c:\Scripts\script4.sql

Save the Main.sql in c:\scripts itself.

C) Create a batch file named "ExecuteScripts.bat" with the following:-

SQLCMD -E -d<YourDatabaseName> -ic:\Scripts\Main.sql
PAUSE

[Remember to replace with the database you want to execute your scripts. For example, if the databese is "Employee", the command would be following ]

SQLCMD -E -dEmployee -ic:\Scripts\Main.sql
PAUSE

D) Execute the batch file by double clicking the same.

Answer 3

Use FOR. From the command prompt:

c:\>for %f in (*.sql) do sqlcmd /S <servername> /d <dbname> /E /i %f