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.
| Command | Description |
|---|---|
| GO [ count] | Executes the current batch [count] times. |
| :List | Prints the contents of the statements cache. |
| [ :] RESET | Clears statement cache. |
| :Error <filename> | Redirects error messages to <filename>. |
| [ :] ED | Starts a text editor. (Usually edit.exe, so this command may be just for backward compatibility). |
| :Out <filename> | Redirects the output to <filename>. |
| [ :] !!<command> | Executes the shell command specified on <command>. |
| :Perftrace <filename> | Creates and redirects performance trace information and saves it into <filename>. |
| [ :] QUIT | Exits sqlcmd. |
| :Connect <server\instance> | Connects to the specified target. |
| [ :] EXIT [statement] | Allows you to use the result of a SELECT statement as the return value from sqlcmd. |
| :On Error [ exit | ignore] | Sets the behavior of sqlcmd regarding to errors. |
| :r <filename> | Loads the content of <filename> into the statement cache. If the file does not contain a line with GO statement, then you have to enter it manually in order to execute the file contents. |
| :Help | Displays sqlcmd help. |
| :ServerList | Lists SQL Servers on the network. |
| :XML [ ON | OFF] | Handles XML output format. |
| :Setvar [Variable] [Value] | Set/unset user variables. |
| :Listvar | Shows a list of the currently set scripting variables. |
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.
| Variable | Read - Write | Default Value | Description |
|---|---|---|---|
| SQLCMDUSER | Read Only | N/A | Current user login name. |
| SQLCMDPASSWORD | N/A | N/A | Current user password. |
| SQLCMDSERVER | Read Only | “DefaultLocalInstance” | The name of the instance and server you are connected to. |
| SQLCMDWORKSTATION | Read Only | “ComputerName” | Your computer name. |
| SQLCMDDBNAME | Read Only | N/A | The name of the database you specified with the -d switch. |
| SQLCMDLOGINTIMEOUT | Read Write | “8” (seconds) | Time before the login attempt fails. |
| SQLCMDSTATTIMEOUT | Read Write | “0” = wait indefinitely | Query timeout. |
| SQLCMDHEADERS | Read Write | “0” | Number of lines to print result set headers. |
| SQLCMDCOLSEP | Read Write | ” ” | Column separator character. |
| SQLCMDCOLWIDTH | Read Write | “0” | Output column width. |
| SQLCMDPACKETSIZE | Read Only | “4096” | The data transfer unit size used by sqlcmd. |
| SQLCMDERRORLEVEL | Read Write | “0” | Level of error message shown. |
| SQLCMDMAXVARTYPEWIDTH | Read Write | “256” | This is the limit of characters used to display variable data types like varchar or json to enumerate a few. |
| SQLCMDMAXFIXEDTYPEWIDTH | Read Write | “0” = unlimited | This is the limit of characters used to display fixed data types, like varchar(n) or numeric to enumerate a few. |
| SQLCMDEDITOR | Read Write | “edit.com” | Default text editor. |
| SQLCMDINI | Read Only | “” | Startup script name. |
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.
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.
- MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019
