Run sql in batches from a folder #sql #SQLSERVER #TSQL

sqlcmd_batch.bat

:: 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