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?
Add a comment
|
5 Answers
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.
2 Comments
Binary Worrier
A down vote with no comment? How can this be? Seriously, if you don't like my answer, or there's something wrong with it can you let me know? We're all here to learn.
mbmihura
Try to use directories and file names without spaces. (it may be handy den4b.com/?x=downloads&product=renamer)
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"
)
1 Comment
Carl G
Adding the following line after the
SETs ensures that there is a trailing backslash: `IF NOT %ScriptsPath:~-1% == "\" SET ScriptsPath=%ScriptsPath%`for %f in ("c:\path\to\dir\*.sql") do sqlcmd -S [SERVER_NAME] -d [DATABASE_NAME] -i "%f" -b
Comments
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 /?.
Comments
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"
