Variables in Microsoft SQLServer scripts

In Oracle you can create a script that takes configurable variables using the '&&var_name' syntax.

In SQLserver it is slightly different. The variables are defined using the syntax

:setvar var_name var_value

Then they are used using the syntax

$(var_name)

IE:

CREATE TABLE $(var_name)

I have about ten scripts that all want the same selection of variables. First off I
duplicated the 'setvar' commands in each script but that is rubbish.

A better solution is to pull all these out into a separate file.
EG c:/TEMP/my_sqlserver.properties

And then in the script you can refer to this file with the syntax

:r C:/TEMP/my_sqlserver.properties

Furthermore I could have a master script that calls all the scripts using the syntax:

:r C:/TEMP/my_sqlserver.properties
:r C:/TEMP/script_1.sql
:r C:/TEMP/script_2.sql

The one major gotcha is that these have to be run in SQLCMD mode.
To run the scripts from within SQL Server Manager Studio click on the SQLCMD Mode button. That's the one with the red exclamation mark before you execute the script.

There are more MS SQL tips here.