Using SQL Server sqlcmd scripting variables

By: Daniel Farina   |   Updated: 2017-08-15   |   Comments (1)   |   Related: More > Database Administration

Problem

SQLCMD is not just a script execution tool. It gives us access to internal variables and commands that we can use to automate code execution in SQL Server. In this tip I will show you how you can use the SQLCMD scripting variables.

Solution

The utility SQLCMD, has been around since SQL Server 2005 as a replacement for osql. Usually sqlcmd was overlooked by the majority of Database Administrators, but with the arrival of SQL Server on Linux this is changing. That’s because on Linux it is very common to use the console to do administrative tasks. In fact, you can do anything with the console.

SQLCMD Commands

An interesting feature of SQLCMD is the ability to use commands inside a script and in the console mode. For example if you are writing code in console mode and you need to see the operating system environment variables or a file name you don’t need to exit the tool or abort what you are doing or end your current batch with GO.

You can just run something like this:

SELECT @@SERVERNAME;
:listvar
GO   

On the next screen capture you will see the output of the previous script using sqlcmd. Notice that you can use sqlcmd commands while you are writing a batch.

For those of you who know something about C programming, the sqlcmd commands are like preprocessor directives. In other words, they change the way the script is executed.

The following table includes the available sqlcmd commands and descriptions. The commands are case insensitive.

SQLCMD Internal Variables

There is an advantage of using scripts with sqlcmd internal variables over pure T-SQL scripts, which is portability. Imagine a scenario where you have a script that needs the name of the current server or the workstation where the script is being executed. In this situation, if you use a pure T-SQL script you will need to modify the references to the server and workstation in the entire script. This is not only a time consuming task, it is also prone to mistakes. Fortunately sqlcmd has internal variables to aid us on this matter.

The following table shows a list of the internal variables for sqlcmd and the descriptions. The variable names are case insensitive.

When you need to reference a variable, either internal or user defined, you must use the following notation where VARIABLE is the variable name:

$(VARIABLE)   

Consider the following script as an example:

SELECT top 5 session_id, login_name 
FROM sys.dm_exec_sessions 
WHERE login_name = '$(SQLCMDUSER)'   

In the previous script we are querying sys.dm_exec_sessions system view for open sessions by the user who runs the script.

Notice that I have enclosed the variable with quotation marks. That’s because the interpretation of the variable relies on sqlcmd. In other words, there is no difference for the SQL Server instance if you use the variable or just write the login name.

The following screen capture shows the output of the previous query.

Next Steps

• Read my previous tip for an introduction to the sqlcmd utility: Introduction to SQL Server's sqlcmd utility
• Also you can read this other tip I wrote about executing script files with sqlcmd: Execute SQL Server Script Files with the sqlcmd Utility.
• If you need to execute multiple files with sqlcmd, you can take a look at this tip: Using SQLCMD to Execute Multiple SQL Server Scripts.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips