Free SQL Help for Devs, DBAs, and the Curious

SQLCMD mode in SQL Server 2019 is an interactive command line tool for SQL Server that allows users to execute Transact-SQL commands directly, execute script files, and execute commands in an operating system. One of the powerful features of SQLCMD mode is the use of variables, which can be very useful in deployment scripts.

SQLCMD variables are defined using the ":setvar" command and can be used anywhere within your T-SQL script. This feature can be quite handy when it comes to writing scripts that are re-usable, dynamic, and configurable. Here's an example showing how to define and use SQLCMD variables:

-- Define SQLCMD variables

:setvar DatabaseName "TestDB"

:setvar TableName "Employees"



-- Use SQLCMD variables

USE $(DatabaseName)

SELECT * FROM $(TableName)

MySQL 8.0 does not support SQLCMD, but it does have a similar feature that allows users to create and use variables within a session. These variables can be used for similar purposes in deployment scripts. Here's an example:

-- Define variables

SET @DatabaseName = 'TestDB';

SET @TableName = 'Employees';



-- Use variables

USE @DatabaseName;

SELECT * FROM @TableName;

DB2 11.5 also allows users to create and use variables within a session. However, the syntax is slightly different. Here's an example:

-- Define variables

DECLARE @DatabaseName VARCHAR(30) DEFAULT 'TestDB';

DECLARE @TableName VARCHAR(30) DEFAULT 'Employees';



-- Use variables

CONNECT TO @DatabaseName;

SELECT * FROM @TableName;

Azure SQL and Azure Synapse, being cloud-based, offer the same SQLCMD mode as SQL Server, allowing users to define and use SQLCMD variables in their deployment scripts. This makes it easier for developers and database administrators to migrate their scripts from on-premise SQL Server to Azure SQL or Azure Synapse.

In conclusion, SQLCMD variables, or a similar feature in other database management systems, are a powerful tool in creating dynamic and re-usable SQL scripts for database deployment. They provide flexibility and efficiency, reducing the time and effort required for database administration tasks.