Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Run all SQL files in a directory

People also ask

How can I execute a set of .SQL files from within SSMS?

While SQLCMD.exe is the best way, SSMS also has a SQLCMD mode where you can execute a SQLCMD script. To enable this mode click Query in menu bar then select SQLCMD Mode. The ":r filename. sql" command is the SQLCMD script command to import and execute a sql script file.


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!


Use FOR. From the command prompt:

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

  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.


The easiest way I found included the following steps (the only requirement is it to be in Win7+):

  • open the folder in Explorer
  • select all script files
  • press Shift
  • right click the selection and select "Copy as path"
  • go to SQL Server Management Studio
  • create a new query
  • Query Menu, "SQLCMD mode"
  • paste the list, then Ctrl+H, replace '"C:\' (or whatever the drive letter) with ':r "C:' (i.e. prefix the lines with ':r ')
  • run the query

It sounds long, but in reality is very fast.. (it sounds long as I described even the smallest steps)


You could use ApexSQL Propagate. It is a free tool which executes multiple scripts on multiple databases. You can select as many scripts as you need and execute them against one or multiple databases (even multiple servers). You can create scripts list and save it, then just select that list each time you want to execute those same scripts in the created order (multiple script lists can be added also):

Select scripts

When scripts and databases are selected, they will be shown in the main window and all you have to do is to click the “Execute” button and all scripts will be executed on selected databases in the given order:

Scripts execution


General Query

save the below lines in notepad with name batch.bat and place inside the folder where all your script file are there

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

EXAMPLE

for %%G in (*.sql) do sqlcmd /S NFGDDD23432 /d EMPLYEEDB -i"%%G" pause

sometime if login failed for you please use the below code with username and password

for %%G in (*.sql) do sqlcmd /S SERVERNAME /d DBNAME -U USERNAME -P PASSWORD -i"%%G"
pause

for %%G in (*.sql) do sqlcmd /S NE8148server /d EMPLYEEDB -U Scott -P tiger -i"%%G" pause

After you create the bat file inside the folder in which your Script files are there just click on the bat file your scripts will get executed