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

Question

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?

Solution 1

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.

Solution 2

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"

  )

Solution 3

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