Dos command to execute all sql script in a directory and subdirectories

Question

Hi, I need a DOS command or a batch (.bat) file I can execute to run all the *.sql script in a directory and it's subdirectories. Any ideas?

Answer 1

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 /?.

Answer 2

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.

Answer 3

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

Answer 4

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"

  )