The Usefulness of a Little R in SQL

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.

00_Main.sql

: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

01_CreateTableCustomer.sql

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

02_DeleteProcCustomer.sql

USE Test_R
GO
CREATE PROCEDURE dbo.DeleteCustomer_SP
@CustomerName VARCHAR(50)
AS
BEGIN
	DELETE FROM
		dbo.Customer
	WHERE CustomerName = @CustomerName
	COMMIT;
END;
GO

03_InsertTableCustomer.sql

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