SQLCMD and its usage in the Management Studio Query Editor


I have been getting used to using Datadude (a.k.a. Visual Studio Team System 2008 Database Edition) in the recent days (I jumped directly onto the Datadude 2008 bandwagon).

Coming to the topic… When generating schema scripts, let’s say to create a new database or alter an existing one, datadude uses some SQLCMD codes. It uses these, for example to set the database name in a parameterized fashion. Like this:

:setvar DatabaseName “MyDatabase”
:setvar DefaultDataPath “D:\Databases\”

CREATE DATABASE [$(DatabaseName)]
NAME = [$(DatabaseName)],
FILENAME = ‘$(DefaultDataPath)My_Database.mdf’,
SIZE = 51200 KB,
NAME = [$(DatabaseName)_log],
FILENAME = ‘$(DefaultDataPath)My_Database.ldf’,
SIZE = 102400 KB,
MAXSIZE = 2097152 MB,

When you need to parameterize stuff such as database file paths or database names in your create database script or something you may be in for a fix when using the usual T-SQL parameters, and may need to write a little more complex T-SQL for the task, such as dynamic SQL which I feel is somewhat unnatural. Using SQLCMD, therefore makes it quite simple and clean. What hurts me though, is that SQLCMD has been around since SQL Server 2005, and I have only just adopted it.

When using SQLCMD in the Query Editor of Management Studio, it would not work under the default settings. You would as a result need to enable SQLCMD mode from the Query menu.

Enable SQLCMD in Query Editor

The primary usage of SQLCMD though, is for command prompt based queries. Yet, it also comes in quite handy in times like these mentioned earlier.