BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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
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:

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