While writing a complicated SQL script recently I wanted to be able to test it on my local system. The script moved data from one database to another. Rather than having to go through the whole script once it was tested to change the local database name to the live name, my aim was to be able to use a variable as the database name.
I discovered that this is possible in management studio using the SQLCMD Mode, this is activated in the query menu:
Once you have activate this mode you can specify a variable as the database name using the following code:
:setvar auratelDB "databaseName"
Then to use this in the code you simply use this variable with a dollar sign before where you would have used the old database name as shown below:
ALTER TABLE $(auratelDB).[dbo].[NetworkProvider] ADD CONSTRAINT [Example_Constraint]
DEFAULT ((0)) FOR [Deleted]
This is a really useful and powerful feature of Sql Management Studio. When I want so use this sql script with another database, now I only have to change one line of code, rather than a hundred.