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.