SQL SERVER Management Studio and SQLCMD Mode

Earlier today when I was helping my clients with Comprehensive Database Performance Health Check, they mentioned that they really enjoyed reading my recent series on SSMS, oStress, and sqlcmd. They really want to try it out sqlcmd option for the automation. However, their real challenge is the command prompt which makes coding and testing SQLCMD difficult. Honestly, there is an alternative way to quickly use SSMS SQLCMD mode as a development environment, let us learn about it today.

SQL SERVER Management Studio and SQLCMD Mode sqlcmdmode0-800x393

Read the following blog posts before getting started with this blog for a better understanding of the topic and also learning about how you can get started with SQLCMD.

Now let us assume that you want to run the following query in the sample database WideWorldImporters.

 SELECT TOP 10 OrderID, OrderDate, ExpectedDeliveryDate
FROM WideWorldImporters.Sales.Orders

Now it is pretty straight forward to run the query in SSMS but if you want to run with SQMCMD in the command prompt you can do that with either just running this query as it is or using variables. Let us see how we can run the query with the help of a few variables in the command prompt.

First, connect to the command prompt.

sqlcmd -S "Quick\SQL19" -d SQLAuthority -E

Next, run the command with the variables.

:setvar Database WideWorldImporters
:setvar Table Sales.Orders
:setvar Cols "OrderID, OrderDate, ExpectedDeliveryDate"
USE $(Database)
SELECT TOP 10 $(Cols) FROM $(Table)
GO

Here is the image of how the command prompt will look with variables.

SQL SERVER Management Studio and SQLCMD Mode sqlcmdmode1

Now it could be very difficult to change anything in the command prompt if you have made any mistakes earlier. As you will have to re-type everything.

Thankfully SQL Server Management Studio (SSMS) has already built-in features to support the Command Prompt. You can enable that by going to Query Menu select selecting the option of SQLCMD Mode.

SQL SERVER Management Studio and SQLCMD Mode sqlcmdmode2

Once you enable that mode you can easily go to start using the SSMS query window as the SQLCMD prompt.

Whenever you specify any variables they will be automatically differently color-coded to give you better query writing experience. With the help of SSMS, now you can go and make any modification in various variables without typing out the entire thing again.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, , , , , , , ,
Previous Post
SQL SERVER – Turning OFF or ON Query Store for All the Database
Next Post
SQL SERVER – Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY

Related Posts

Leave a Reply

Menu