Month: June 2016

SSMS – Running scripts in SQLCMD Mode

Standard

According to MSDN, SQLCMD can be used to process Windows System commands and Transact-SQL statements in the same script.

One of the most useful things you can do is pass parameters into your script from the command line by using the :setvar command and use them to replace sections of your script, such as user names. Variables set when using :setvar cannot be updated as they are passed into the script when it is called.

You can turn on SQLCMD in SQL Server Management Studio by selecting SQLCMD Mode form the Query menu:

USE [BestDatabase]
GO

:setvar ENV INT

IF DATABASE_PRINCIPAL_ID('BestDomain\BD_Svc$(ENV)') IS NULL
BEGIN
	CREATE USER [BestDomain\BD_Svc$(ENV)] FROM LOGIN [BestDomain\BD_Svc$(ENV)];
END

GO

The above script accepts the parameter ENV and uses it to replace $(ENV) with the value of the parameter, in this case INT. As you can see the above script, when executed, will add the SQL Server login BestDomain\BD_SvcINT, to the BestDatabase database.

I’m sure you can imagine the rest of the possiblities!