Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using SQL Server sqlcmd scripting variables


By:   |   Read Comments (1)   |   Related Tips: More > Database Administration

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

sqlcmd - Description: You can use sqlcmd commands without ending the current 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.

Sqlcmd Variables - Description: Using internal variables whithin the T-SQL code.
Next Steps


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, August 19, 2017 - 2:10:45 PM - Rick Willemain Back To Top

 Very nicely written quick, summary article.  Very helpful !  Thank you !!

 


Learn more about SQL Server tools