How SQLCMD in SSMS can help you?

SQLCMD mode allows to you combine Windows System
commands and T-SQL commands in the same batch.

First of all to enable it, click on SQLCMD icon in the toolbar:

OK, now let`s see what it can do for us:

1) You can easily save a query`s result set to a file:

:out C:\Temp\outputfile.txt
SET NOCOUNT ON
SELECT * FROM dbo.SomeTable
GO

Pay attention, the file will be saved on your local drive
and not on the SQL Server machine (Of course if you
execute this not on SQL Server box)
Tip: the column delimiter can be defined in Tools->Options:

2) You can execute script file(s) directly from the SSMS:


:r c:\scriptFile.sql

3) You can execute a DOS commands directly from the SSMS:


!!dir c:\Temp
!!type  c:\temp\DemoFile.txt

!!ipconfig

and even execute a program:

!!notepad

4) You can change SQL connection:


:connect ProdServer


5) You can set variables and use them later


This is very cool, it allows us to create complex dynamic scripts:

Here simple example:

:setvar DBname Master

:setvar TableName sys.tables

USE $(DBname)
GO
SELECT *
FROM $(TableName)

Go ahead and try it !!!
What I gave as examples is just to demonstrate and introduce
to you the tool. You can get much more interesting examples
and usages here or here.

Tweet