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?

Reeder

Answer 1

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!

Riella

when i executed the batch file some authentication problem occurs saying "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.". Is there a way to provide username and password too like as server name and database? – Hypogastrium

@SanjayMaharjan use -U for user and -P for password, like: for %%G in (*.sql) do sqlcmd /S servername /d databaseName -U username -P "password" -i"%%G" – Lubra

how can i put the output to separate files using -o ? whenever i use like -o temp.txt, this temp.txt is overwritten. i want to get the output files as same sql file name. – Vardhamana

@vijeth: good work man . i was thinking of doing one by one some 200 sql files. saved a lot of time – Armand

@Riella thank you. i just modify for myself : REM REM development environment only!! REM pause for %%G in (*.sql) do sqlcmd /S "192.168.10.139\SQLEXPRESS" /d "TESTDEV_DB" -U "atiour" -P "atiour" -i"%%G" pause REM REM All Script Run Successfully REM – Abridgment

If you are not getting any results by double clicking, try opening Command Prompt as Administrator go to the folder and run the bat file. – Indigestible

How to give the full path where .Sql files present not only drive path. Please let me know. – Psychopath

Answer 2

Use FOR. From the command prompt:

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

Didactic

I had to add quotes around the last "%f" to make it work with scripts that contain spaces – Ecosphere

For a version that works in batch files, see this answer. – Benign

I also had to add some other params (e.g. /I to enable quoted identifiers) – Prunelle

i like this one - but is there any way to have the results output to a file? so i can easily see any exceptions? i just tried this command on 136 .sql files, so i've lost visibility of most of them – Franfranc

@Franfranc To redirect output to file, use this command: for %f in (*.sql) do sqlcmd /S <servername> /d <dbname> /E /i "%f" >> sql.log 2>&1) You can read more about redirection of output here – Kerstin

Answer 3

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

Diahann

It's an amazingly fast way! – Sinhalese

This was a fast and easy way. Good tip! But I had to either remove or add the '\' in the replace bullet. – Arbitrage

it's a life saver.. – Burglarize

What about stored procedures? Is there a similar way to run SPs? – Tyishatyke

we need to remove and replace the double quote with space too – Lachus

In fact no.. we shouldn't remove the double quotes.. You would get 'Incorrect syntax was encountered while parsing :r .' if you remove the quotes. – Diahann

This is good, just need to double check the order, I find it's never the same as the order or the files for whatever reason – Impasto

@SuyashGupta sqlcmd can run any file. if your sp is scripted, you can run it. This is an good poor mans change control. you have to maintain the files of course, but you can run multiple files to a testDB, test the changes, then run the same changes to prod and be confident the same thing happened. – Epicycle

You can avoid the find replace by holding down ALT and dragging down the column at the start of the line to block insert :r – Tentmaker

Answer 4

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

Mastitis

this is really tedious if I have hundreds of files. – Swaziland

@devlincarnate: Presumably you can come up with a way to automate step 1. Such as "dir /B *.sql > list.txt", and then massage that list.txt file a bit. – Hardee

@devlincarnate in newer versions of Windows, you can hold down the Shift key, right-click a file, and select "Copy as path". From there, CTRL+V into an SSMS window. It works with multiple files too. Select two or more files in Explorer, right-click any of the highlighted files, and select "Copy as path". Repeat steps in SSMS. File paths are enclosed in double-quotes, which you may or may not want to strip out in SSMS with Find/Replace. – Giaour

invalid syntax in mssql 2016 – Squirrel

Answer 5

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.

Peephole

I just edited my answer. Also, one needs to make sure the script files exist in the specified path. – Peephole

the good thing about this approach is that any error is found then stop executing further scripts :) similar to -b example: SQLCMD -b -i "file 1.sql","file 2.sql" – Foeticide

and the bad thing about this approach that one has to maintain the list of all SQL files to be ran. – Andros

creating a batch file seems like extra, what's the point in that step? it can be accomplished by stopping at step 2. – Squirrel

Answer 6

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

Brandie

Answer 7

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):

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:

Germ

Answer 8

I wrote an open source utility in C# that allows you to drag and drop many SQL files and start running them against a database.

The utility has the following features:

• Drag And Drop script files
• Run a directory of script files
• Sql Script out put messages during execution
• Script passed or failed that are colored green and red (yellow for running)
• Stop on error option
• Open script on error option
• Run report with time taken for each script
• Total duration time
• Test DB connection
• Asynchronus
• .Net 4 & tested with SQL 2008
• Single exe file
• Kill connection at anytime

Analyse

Answer 9

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

Amarillis

Answer 10

@echo off
cd C:\Program Files (x86)\MySQL\MySQL Workbench 6.0 CE

for %%a in (D:\abc\*.sql) do (
echo %%a
mysql --host=ip --port=3306 --user=uid--password=ped < %%a
)

Step1: above lines copy into note pad save it as bat.

step2: In d drive abc folder in all Sql files in queries executed in sql server.

step3: Give your ip, user id and password.

Bothy

This question is relating to MSSQL , not MySQL. – Boyar

Answer 11

For executing every SQLfile on the same directory use the following command:

ls | awk '{print "@"$0}' > all.sql

This command will create a single SQL file with the names of every SQL file in the directory appended by "@".

After the all.sql is created simply execute all.sql with SQLPlus, this will execute every sql file in the all.sql.

Changsha

Answer 12

I know this question is more focused on SQL Server. I had the same question, but for PostgreSQL. The solution is very close for what I needed, so I thought I would share what I got for anyone that needs it:

for %f in (*.sql) do psql -U [username] -d [database name] --command="\i %f";

I ran this from the folder containing all of my sql scripts.

To avoid being prompted for a password, I had to add

*:*:*:[user]:[password]

to my pgpass.conf file that lives in

%APPDATA%\Roaming\postgresql\ 

folder on windows. I had to create the file myself.

Dickie

Answer 13

You can create a single script that calls all the others.

Put the following into a batch file:

@echo off
echo.>"%~dp0all.sql"
for %%i in ("%~dp0"*.sql) do echo @"%%~fi" >> "%~dp0all.sql"

When you run that batch file it will create a new script named all.sql in the same directory where the batch file is located. It will look for all files with the extension .sql in the same directory where the batch file is located.

You can then run all scripts by using sqlplus user/pwd @all.sql (or extend the batch file to call sqlplus after creating the all.sql script)

Cellulous

Answer 14

If you can use Interactive SQL:

1 - Create a .BAT file with this code:

@ECHO OFF ECHO
for %%G in (*.sql) do dbisql -c "uid=dba;pwd=XXXXXXXX;ServerName=INSERT-DB-NAME-HERE" %%G
pause

2 - Change the pwd and ServerName.

3 - Put the .BAT file in the folder that contains .SQL files and run it.

Configurationism