CODEWITHSUNDEEP
sqlt-sqlbatch-filedos
Uince
Uince

Reputation: 193

DOS command to execute all SQL script in a directory and subdirectories

I need a DOS command or a batch (.bat) file I can execute to run all the *.sql scripts in a directory and its subdirectories. What would the solution be?

Upvotes: 3

Views: 13031

Answers (5)

Nishantha
Nishantha

Reputation: 87

SET Host=pmegat
SET Database=pdb
SET user=usr
SET pwd=abc123
SET ScriptsPath=C:\Work\Scripts

IF EXIST "%ScriptsPath%output.sql" del "%ScriptsPath%output.sql"

echo SPOOL Script.log >> "%ScriptsPath%output.sql"

FOR /R "%ScriptsPath%" %%G IN (*.sql) DO (

echo Prompt execute @"%%G" >> "%ScriptsPath%output.sql"
echo @"%%G" >> "%ScriptsPath%output.sql"

)
echo SPOOL OFF >> "%ScriptsPath%output.sql"
echo exit >> "%ScriptsPath%output.sql"

sqlplus %user%/%pwd%@%Host%:1521/%Database% @"%ScriptsPath%output.sql"

Upvotes: 0

Blopster75
Blopster75

Reputation: 31

for %f in ("c:\path\to\dir\*.sql") do sqlcmd -S [SERVER_NAME] -d [DATABASE_NAME] -i "%f" -b

Upvotes: 3

alex
alex

Reputation: 31

Here you go. This batch file will execute all sql files in a directory and its subdirectories. It will also create an output.txt file with the results so you can see errors and whatnot. Some notes on batch file:

  • [YourDatabase] is the name of the database you want to execute the scripts against.
  • [YourPath] is the path of where you keep all the scripts.
  • [YourServerName\YourInstanceName] is the SQL server name and instance name, separated with a '\'
  • You'll want to replace the text after the '=' for each variable with whatever is appropriate for your server
  • Be sure NOT to put spaces around the '='
  • Do not put any quotes around [YourPath]

  • Make sure that [YourPath] has a '\' at the end

    SET Database=[YourDatabase]

    SET ScriptsPath=[YourPath]

    SET ServerInstance=[YourServerName\YourInstanceName]

    IF EXIST "%ScriptsPath%output.txt" del "%ScriptsPath%output.txt"

    type NUL > "%ScriptsPath%output.txt"

    FOR /R "%ScriptsPath%" %%G IN (*.sql) DO (

    sqlcmd -d %Database% -S %ServerInstance% -i "%%G" -o "%%G.txt"

    echo ..................................................................................... >> "%ScriptsPath%output.txt"

    echo Executing: "%%G" >> "%ScriptsPath%output.txt"

    echo ..................................................................................... >> "%ScriptsPath%output.txt"

    copy "%ScriptsPath%output.txt"+"%%G.txt" "%ScriptsPath%output.txt"

    del "%%G.txt"

    )

Upvotes: 3

Binary Worrier
Binary Worrier

Reputation: 51711

The following will get you started

for /r %f in (*.sql) do echo %f

Run from the command line that will print the names of all the SQL files in the current directory and all sub directories.

Then substitute sqlcmd <connection args> -i%f for echo %f to execute the scripts.

Hope this helps.

Upvotes: 6

itub
itub

Reputation: 1189

Try a for loop. The options of this command have evolved and I'm not sure what version of DOS you are using, but assuming that DOS includes "cmd.exe from Windows XP", something like this could work:

for /r . %f in (*.sql) do @echo %f

Ok, this will only print the names of the files. I'm assuming you already have a program that you can run from the command line that will execute one SQL file, which you can use instead of echo.

For more information, try for /?.

Upvotes: 0

Related Questions