Run all SQL files in multiple directories

Question

I combined a couple of solutions I found online to try and make this happen.

https://stackoverflow.com/a/6504317/2471473

https://sqlandme.com/2013/03/25/sql-server-executing-multiple-script-files-using-sqlcmd/

I'm trying to run a single .cmd script (Script1.cmd) with folder locations of .sql files. That single script runs another script (Script2.cmd) to use sqlcmd to execute all the .sql files in that folder location. It mostly works, but it leaves a command window open that I have to exit from for each folder location.

Script1.cmd

start Script2.cmd "C:\Location1"
start Script2.cmd "C:\Location2"

Script2.cmd

@Echo Off
FOR /f %%i IN ('DIR %1\*.Sql /B') do call :RunScript %1 %%i
GOTO :END

:RunScript
Echo Executing Script: %2
cd %1
SQLCMD -S Server123 -d Database456 -E -i %2
Echo Completed Script: %2

:END

Do you really want both `Script2.cmd` command lines to run simultaneously? — aschipfl, Apr 19 '16 at 20:57
Yes: to run one after another, simply use `call Script2.cmd *`... you could also use `start "" /WAIT cmd /C Script2.cmd *`, if you want a new window to be opened for every run... — aschipfl, Apr 19 '16 at 23:03

score 2 · Accepted Answer · answered Apr 19 '16 at 20:26

Official command line reference for Windows XP or for Windows Server 2003, Windows Vista (and above) seems to be too brief. Read this (extended) start command documentation:

Syntax: START "title" [/D path] [options] "command" [parameters]

Always include a TITLE this can be a simple string like "My Script" or just a pair of empty quotes "". According to the Microsoft documentation, the title is optional, but depending on the other options chosen you can have problems if it is omitted.

If command is an internal cmd command or a batch file then the command processor is run with the /K switch to cmd.exe. This means that the window will remain after the command has been run.

Next Script1.cmd should work and close started command windows:

start "" cmd /C Script2.cmd "C:\Location1"
start "" cmd /C Script2.cmd "C:\Location2"

Run all SQL files in multiple directories

1 Answers1