How to create Batch file to execute sql scripts ?

Question :

Hi friends,  

      i want to create batch file to execute all my .sql scripts.

I have all table ( all table scripts in single file ) ,Udds ( all udds in single file ) ,Stored procedures( separate file for each SPs ),Functions ( Separate file for each Functions ),Triggers and views scripts in .SQL file.  

can anybody tell me how to create batch file for executing all these scripts in sql server ?.  

   while executing, it should ask Database name,server name, password. if these details are given then it should execute my all scripts in given database

, if any error thrown then that error and procedure name alone have to move to separate log file..

Answer :  

1. Open New notepad 

2. Paste the below code and change content as per your requirement

@echo off
cls

echo *******************************************************************************
echo *                     DATABASE DEPLOYMENT                    *
echo *******************************************************************************
echo *                     WARNINGS                                        *       
echo *******************************************************************************
echo *  1. You can give some warnings like take backup before executing             *
echo *******************************************************************************


set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p DbName=Database Name :


set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?

if '%choice%'=='y' goto begin
goto end

:begin
if exist _Deploy.txt del _Deploy.txt

@echo on



@echo UDDs >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "UDDs"\UDDs.sql >> _Deploy.txt 2>&1


@echo TABLES >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Tables"\TABLE_SCRIPT.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Tables"\Insert_Script.sql >> _Deploy.txt 2>&1


@echo FUNCTIONS >>_Deploy.txt
@echo ******************* >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Functions"\TEST_FN.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Functions"\TEST1_FN.sql >> _Deploy.txt 2>&1


@echo STORED PROCEDURES >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Stored Procedures"\CheckInternalmachineType_SP.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Stored Procedures"\CheckLineType_SP.sql >> _Deploy.txt 2>&1


@echo TRIGGERS >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Triggers"\Master_TR.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Triggers"\Price_Master_TR.sql >> _Deploy.txt 2>&1


@echo VIEWS >>_Deploy.txt
@echo ***************** >>_Deploy.txt
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Views"\Gate_Pass_Rpt_VW.sql >> _Deploy.txt 2>&1
sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i "Views"\GatePass_VW.sql >> _Deploy.txt 2>&1

@notepad _Deploy.txt

:end

 

 3. then Save this file as filename.bat

4. now execute this by giving correct server details . if any error then it would write in _Deploy.txt.