Run all SQL files in a directory

1. In the SQL Management Studio open a new query and type all files as below

   :r c:\Scripts\script1.sql
   :r c:\Scripts\script2.sql
   :r c:\Scripts\script3.sql
   

2. Go to Query menu on SQL Management Studio and make sure SQLCMD Mode is enabled

3. Click on SQLCMD Mode; files will be selected in grey as below

   :r c:\Scripts\script1.sql
   :r c:\Scripts\script2.sql
   :r c:\Scripts\script3.sql
   

4. Now execute

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

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

2. 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.

3. Create a batch file named ExecuteScripts.bat with the following:

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

   Remember to replace <YourDatabaseName> with the database you want to execute your scripts. For example, if the database is "Employee", the command would be the following:

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

4. Execute the batch file by double clicking the same.

Use FOR. From the command prompt:

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

Create a .BAT file with the following command:

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause

If you need to provide username and passsword

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -U username -P 
password -i"%%G"

Note that the "-E" is not needed when user/password is provided

Place this .BAT file in the directory from which you want the .SQL files to be executed, double click the .BAT file and you are done!