This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. What is SQLCMD mode, and how can you use it? Invoking SQLCMD mode Examples of the use of SQLCMD mode (this blog)

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.

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

Posted by Andy Brown on 24 November 2021

In this blog

Connecting to databases
Setting variables to connect to different databases
Setting variables to run different queries
Running Windows commands
Redirecting output to file

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:

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:

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:

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:

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:

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:

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

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

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.