Skip to content

Instantly share code, notes, and snippets.

@brunotdantas
Last active March 3, 2020 18:16
Show Gist options
  • Save brunotdantas/28709e1f09c3110dd492568510ed2cd3 to your computer and use it in GitHub Desktop.
Save brunotdantas/28709e1f09c3110dd492568510ed2cd3 to your computer and use it in GitHub Desktop.
Run sql in batches from a folder #sql #SQLSERVER #TSQL
:: Source: https://www.sqlservercentral.com/scripts/run-sqlt-sql-scripts-on-folder
:: A bat script to run all the sql/t-sql scripts in a specific folder.
::
:: Features:
::
:: - Using SQLCMD for running the scripts.
::
:: - Logging the results of your sql to a file per script.
::
:: - Input for scripts and log folders.
::
:: - Beginning and end times for each script.
::
:: - Check if the folders exist
::
:: - Total running duration of each script saved in the logs.
::
:: Usage:
::
:: - Create a empty .bat or .cmd and copy and paste the code in it.
::
:: - Change your machine and instance name(for clusters) for single instance you just need your machine name.
::
:: - Put your credentials(if needed)
::
:: - Run the script, you will be prompt with the scripts and logs paths(can be the same).
::
:: - Just wait...the bat will do the rest 🙂
::
:: There are other scripts out there that can do this, this just one more, simple and working.
::
:: Use and abuse.
::
:: Daniel Marques
::
:: -- Source: https://www.sqlservercentral.com/forums/topic/run-sqlt-sql-scripts-on-folder
:: -- Source: https://www.sqlservercentral.com/scripts/run-sqlt-sql-scripts-on-folder
::
@echo off
SET /p pathScripts="PATH Scripts: "
IF NOT EXIST "%pathScripts%" (
echo Path not found, create the folder and come back
Exit /b
)
SET /p pathlogs="PATH Logs: "
IF NOT EXIST "%pathlogs%" (
echo Path not found, create the folder and come back
Exit /b
)
cd %pathScripts%
IF NOT EXIST *.sql (
echo No.sql file(s) in this folder, check that and come back
Exit /b
)
FOR %%i IN (*.sql) do call :RunScript %%i
GOTO :END
:RunScript
SET name=%1
SET name=%name:~0,-4%
SET name=\%name%.log
Echo Executing Script: %1
call :StartTimer
echo Begin: %StartTime%
SQLCMD -S MACHINE_NAME\INSTANCE_NAME -i %1 -o "%pathlogs%%name%"
call :StopTimer
Echo Script Done: %1
Echo Log in: %pathlogs%%name%
echo End: %StopTime%
echo Duration: %duration% >> "%pathlogs%%name%"
echo Duration: %duration%
echo ------------------------------------------
:END
:StartTimer
set StartTIME=%TIME:~0,-3%
goto :EOF
:StopTimer
set StopTIME=%TIME:~0,-3%
set /A "ss=(((1%StopTIME::=-100)*60+1%-100)-(((1%StartTIME::=-100)*60+1%-100)"
set /A "hh=ss/3600+100,ss%%=3600,mm=ss/60+100,ss=ss%%60+100"
set Duration=%hh:~1%:%mm:~1%:%ss:~1%
goto :EOF
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment