Technical Article

Run SQL/T-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

@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

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

5 (2)

You rated this post out of 5. Change rating