There are different options available when you want to execute a .sql script file on a server. You can open the script file in SQL Server Management Studio and execute it, or you can use SQLCMD to execute a script from command line. However, when you have large number of scripts to execute these methods may not be feasible.
You can use SQLCMD and batch programming together to execute a large number of script files easily.
Below is the code for batch file:
FOR /f %%i IN ('DIR *.Sql /B') do call :RunScript %%i
Echo Executing %1
SQLCMD -S Server\Instance -U Vishal -P Password -i %1
Echo Completed %1
Copy this to notepad and save it as a .cmd or .bat file, for example save it as "RunMyScripts.cmd" in the same folder where your scripts are stored.
Make sure you replace server details and authentication details.
Then go to command prompt and navigate to this folder and execute “RunMyScripts.cmd”
Hope This Helps!
Filed under: Management Studio, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions