By Rohit Khadka, Sr. Database Administrator
At some point of our DBA career, we all have had an opportunity to run a set of scripts one by one for each instance until your fingers and mouse gave up. Then, we came back to work next day with a bandage wrapped around our fingers and continue doing the same thing all over again.
If you are one of those lucky ones who is working with just a couple of servers or a few scripts, this task may not seem very daunting. Even then, you may wish after some time if there was some way to save your fingers and maybe even save the planet from wasting batteries for your mouse. Well, the good news is: there is a thing called :r within the SQLCmd that really saves time as well as our sanity.
Today, we are going to take a look at how this little r command has the power to change your life and how you do things. The r command essentially tells the SQLCmd to parse the SQL and T-SQL commands within a specified file into the statement cache. In other words, you can have a set of SQL files in a folder and execute them in a single batch by calling one single file which references these files. Also, these scripts can be run in a specific order which saves a lot of headache running the file in the wrong order when done manually.
Here is a sample of a set of scripts that we are going to run on our local instance:
In order to accomplish this task in a single batch, we will be creating another file that will reference these 3 files above. We will just call ‘00_main.SQL’. It looks something like this:
As you can see, we are using a few different commands in the file. Let’s see what each one does:
:connect – This command allows us to connect to the instance.
:setvar – This command sets the variable ‘filepath’ in this case to the directory where we have the scripts located.
:out – This command outputs the log to a text file in this case.
:on error exit – This command will tell the SQLCmd to stop and exit the run if an error is encountered.
:r – This command is telling SQLCmd to parse and execute the file specified next to it.
:quit – This command tells SQLCmd to quit the process once it is complete.
Now that we understand what each of these commands do, let’s execute the script and see what it does.Note: You will need to enable SQLCMD mode in SSMS to run this script, otherwise it will throw an error. Here is how you can Enable SQLCMD in SSMS.
The script executed successfully. I checked the log file to make sure it did not report any errors.
Next, we want to check if all the objects got created in SSMS. Let’s take a look:
Voila! The table, stored procedure and the data is there. Now, if I needed to execute this task on multiple instances or server, I would have had to open each of these 3 files multiple times and make sure that there is no human error (it happens). As we learned the power and usefulness of little r, we can use this command to perform many other types of operations including copying files, creating directories, etc. The r command is probably one of the underused features which not only saves time but as well as makes it easier for DBAs to implement changes across multiple instances/servers. Good luck keeping those fingers safe! 😊
Each script individually available below or zipped together at the end.
:connect *YOUR LOCAL MACHINE*\SQLEXPRESS go --set variable filepath :setvar filepath "C:\temp\RCommand" --output log file :out "C:\temp\RCommand\Log.txt" --exit if there is an error :on error exit go PRINT 'START:Create Customer Table' :r $(filepath)\01_CreateTableCustomer.sql PRINT 'END:Create Customer Table' go PRINT 'START:Delete customer stored procedure' :r $(filepath)\02_DeleteProcCustomer.sql PRINT 'END:Delete customer stored procedure' go PRINT 'START:Insert Data into Customer Table' :r $(filepath)\03_InsertTableCustomer.sql PRINT 'END:Insert Data into Customer Table' go
USE Test_R GO DROP TABLE IF EXISTS dbo.Customer GO CREATE TABLE dbo.Customer ( CustomerID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, CustomerName VARCHAR(50) NOT NULL, IsActive BIT NOT NULL, Created_Date DATETIME2, Created_By VARCHAR(50), Modified_Date DATETIME2, Modified_By VARCHAR(50) ) GO
USE Test_R GO CREATE PROCEDURE dbo.DeleteCustomer_SP @CustomerName VARCHAR(50) AS BEGIN DELETE FROM dbo.Customer WHERE CustomerName = @CustomerName COMMIT; END; GO
USE Test_R GO INSERT INTO dbo.Customer SELECT 'Banana Republic', 1, GETDATE(), SUSER_NAME(), NULL, NULL UNION SELECT 'Old Navy', 1, GETDATE(), SUSER_NAME(), NULL, NULL GO
Download all four in a zip here