SQL SERVER – Enable SQLCMD Mode in SSMS – SQL in Sixty Seconds #048

The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. Often a developer believes that sqlcmd works with only command prompt, however that is not true. It can also work with SQL Server Management Studio. There are lots of cool tricks we can do with it while we are using it along with T-SQL.

One of the tricks which I often use it that I use it to connect to different servers from SQL Server Management Studio while I work with multiple SQL Server instances. This way I do not have to keep on connecting/disconnecting the server using SSMS prompt. This makes things very easy as well it is less distracting. The code which you can use to connect to another server is as follows:

SELECT @@SERVERNAME
GO
:Connect localhost
SELECT @@SERVERNAME
GO

In this SQL in Sixty Seconds video I have demonstrated how we can list Windows Directory while we are working with SSMS. The display of the SQLCMD is visible in the message area of the SSMS result pane. The code of the same is as follows:

USE AdventureWorks2012
GO
SELECT DISTINCT Type FROM Sales.SpecialOffer;
GO
!!DIR
GO
SELECT ProductCategoryID, Name FROM Production.ProductCategory;
GO

This trick is described over here.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

• Introduction and Explanation
• Backup and Restore Database Using Command Prompt
• Exporting Query Results to CSV using SQLCMD
• sqlcmd vs osql – Basic Comparison
• Connecting to Server Using Windows Authentication
• Interesting Observation – Using sqlcmd From SSMS Query Editor
• Using a Dedicated Administrator Connection to Kill Currently Running Query

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (https://blog.sqlauthority.com)