SQLCMD is a command line utility that comes with SQL Server. This CLI can be used to run SQL scripts and/or commands, among other uses. I’ve used the utility in the past, as part of a .Net program to run all of the SQL scripts in a specified directory.

We can also run scripts in SSMS in SQLCMD mode and take advantage of some of its functionality. The job I’m at now makes frequent use of this, to set variables in a script and have them used across several scripts.

Example:
Here’s an example of using a variable in SQL command mode:

:setvar schemaName "TestCo"

DROP TABLE IF EXISTS $(schemaName).[Product];

DROP SCHEMA IF EXISTS $(schemaName);
GO

CREATE SCHEMA $(schemaName);
GO

CREATE TABLE [$(schemaName)].[Product] (
	ProductId int IDENTITY(1,1) NOT NULL,
	[Name] varchar(50) NOT NULL
);

INSERT INTO $(schemaName).[Product] (Name) 
    VALUES ('TestProduct');

SELECT * FROM $(schemaName).[Product];

The SQLCMD commands are prefixed with a colon(:). We also need a SQLCMD command as the first line in the script.
We make use of the schemaName variable with this syntax: $(schemaName).
To run this, we’ll need to enable SQLCMD mode. From the Menu bar, we can select Query, then SQLCMD Mode. Then we can run the script.

We can also reference variables in a different script. Using the same code from above, we’ll save the variable declaration in a script at C:\Temp\Config.sql

:setvar schemaName "TestCo"

We’ll reference that Config script with a run command (r) in a 2nd script:

:r "C:\Temp\Config.sql"

DROP TABLE IF EXISTS $(schemaName).[Product];

DROP SCHEMA IF EXISTS $(schemaName);
GO

CREATE SCHEMA $(schemaName);
GO

CREATE TABLE [$(schemaName)].[Product] (
	ProductId int IDENTITY(1,1) NOT NULL,
	[Name] varchar(50) NOT NULL
);

INSERT INTO $(schemaName).[Product](Name) 
    VALUES ('TestProduct');

SELECT * FROM $(schemaName).[Product];

We can make use of the script several times, and only have to change the variables in one place, rather than updating references across several scripts.

We can also write output to external files, connect to other SQL Server instances, among other actions as well.

Links:

Microsoft – Edit SQLCMD Scripts with Query Editor

Devart – Unlocking the Power of SQLCMD in the SSMS Query Editor