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)]
ON
(
NAME = [$(DatabaseName)],
FILENAME = ‘$(DefaultDataPath)My_Database.mdf’,
SIZE = 51200 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10240 KB
)
LOG ON
(
NAME = [$(DatabaseName)_log],
FILENAME = ‘$(DefaultDataPath)My_Database.ldf’,
SIZE = 102400 KB,
MAXSIZE = 2097152 MB,
FILEGROWTH = 1024 KB
)

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.

Advertisements

5 thoughts on “SQLCMD and its usage in the Management Studio Query Editor

  1. Good topic and you have talked to the point 🙂
    saved my day at office creating dynamic sql script for database generation using bat files 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s