This last few days was a really busy day. Our developer team has plan to apply update to all customers database. They have create a bunch of SQL script file that need to be executed against each customers database. They did create a custom application to execute all the SQL script but I’ve found it difficult to know if a script successfully executed or not.
I was know that there is a way to execute SQL Script manually from SQLCMD but I didn’t like it display with black and white screen. I wonder if there’s a way to execute from SQL Server Management Studio (SSMS) without open the script, and also a way to execute many SQL script files. After a little search I found the answer here. Thanks to the guy who post that answer, he save my day. Even you can find the answer from the above link but I think that I have to write it down here, it can help me to remember when I forget about it, I only need to open my very own blog to find it.
So SSMS it self has SQLCMD mode where we can executed SQL script file. It’s not activated by default. To activate SQLCMD mode on SSMS, after open New Query window, click Query in menu bar, and then select SQLCMD Mode.
The “:r filename.sql” command is the SQLCMD script command to import and execute an SQL script file. You will know you are in SQLCMD mode because any lines that are SQLCMD script commands will appear with gray color in background. Type the following command on that query window then execute the script and it’s executed just like I did open the file in SSMS before execute it.
:setvar path "D:\path-to-scripts-folder\" :r $(path)\file1.sql :r $(path)\file2.sql :r $(path)\file3.sql :r $(path)\file4.sql :r $(path)\file5.sql
You have to change the path folders and SQL file name to match your own. And remember that path files are relative to the machine that running SSMS. If you run above script from SSMS on your PC then the path files was relative to your PC not to your SQL server. Thank you