Quick one today:
Frequently, we run into situations, where we need to put place holders, in a SQL script, that take different values based on some conditions or settings. Essentially having variables about the script itself (and not the code)
We are taking about variables at the meta-level (script file level, and not the variables in the code itself)
SQLCMD mode within SSMS allows us to set placeholders / variables that will take new values (as you provide) right before executing the script.
First enable SQLCMD mode in SSMS:
In the query window, write some code as below:
-- -- Set variables in a script -- :SETVAR DatabaseName "master" :SETVAR TableName "MSreplication_options" USE $(DatabaseName) SELECT TOP 10 * FROM syscolumns USE $(DatabaseName) SELECT * FROM sys.objects WHERE name LIKE '$(TableName)' GO
Observations:
Variable replacement occurs both in
USE $(DatabaseName)
and
WHERE name LIKE ‘$(TableName)‘
- Having single quotes does not affect variable replacement, as this replacement occurs at the script file level (and node code level — if that makes sense).
- Since this is a universal replacement capability, it allows you to use variable replacement anywhere in the code without need for special escape characters.
_Sqltimes
Leave a Reply