Browse 531 attributed reviews, viewable separately for our classroom and online training
What is SQLCMD mode, and how can you use it?
Part three of a three-part series of blogs

You can use SQLCMD mode to parameterise the connection, database name, table name and select columns for a query - and much more besides, as this blog explains.

  1. What is SQLCMD mode, and how can you use it?
  2. Invoking SQLCMD mode
  3. Examples of the use of SQLCMD mode (this blog)

Posted by Andy Brown on 24 November 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Examples of the use of SQLCMD mode

This page is intended to give you ideas!

Connecting to databases

You can temporarily connect to a database to run commands within it:

Connecting to database

The output shows that SSMS automatically disconnects from the database when the script finishes.

 

Setting variables to connect to different databases

If you want to run the same script in different databases, you can now do this easily:

Variables for connection

Here I've gone a bit mad, and have parameterised my connection, schema, database and stored procedure name!

Setting variables to run different queries

You could change the values assigned to the variables below to get different results from your query:

Parametrised query

This query will show the title and release year of each film, sorted by descending order of the release year.

Running Windows commands

You can run operating system commands by preceding them with !!.  For example:

Show files in directory

This command lists out the files in a folder.

 

Redirecting output to file

Use the :out command to send the results from your query to a file:

Text file query

A simple query which will send its output to the given text file.

 

It's a good idea to switch from grid to text format first, to make the output easier to read:

Sending to text file

Click on this tool to switch from grid to text output.

  

For the query above, here's what you might get:

Final rows

I should probably have included the command SET NOCOUNT ON to suppress the message showing the number of rows affected.

 

There are (inevitably) lots of sites which go into much more detail about things which you can do in SQLCMD mode, but most of them are more geared towards SQL Server administrators.  I found this one the most useful.

  1. What is SQLCMD mode, and how can you use it?
  2. Invoking SQLCMD mode
  3. Examples of the use of SQLCMD mode (this blog)
This blog has 0 threads Add post