Here’s a fun little tip you can use in your SQL scripts to make them scriptable via the sqlcmd utility while still supporting SSMS.  Say you want to make the name of your database configurable so you can script out the deployment of your database.  You could do that with a script like this:

   1: SET @dbName = '$(DBNAME)'

   2: ...

   3: EXEC sp_executesql 'CREATE DATABASE [' + @dbName + ']'

   4: ...

But what happens when someone decides to run your script in SSMS?  It will try to create a database named ‘$(DBNAME)’!  That’s not even remotely what we want.

NOTE: Yeah, I know you can make SSMS execute the script in SQLCMD mode, but then it just dies with a ‘variable not defined’ error, which isn’t what we want either.

What we’d like to do is have the script fall back to a default when run in SSMS.  That’s easy to do, just add these two lines of code after your initial SET statement:

   1: if @dbName = '$' + '(DBNAME)'

   2:     SET @dbName = 'DefaultName'

All it’s really doing is checking to see if @dbName is set to the string that would have been replaced by an actual value had the script been run via sqlcmd.  You do have to break it up into two parts though to prevent sqlcmd from performing substitution there, otherwise your sqlcmd script will always use the default name instead of whatever was passed in to it.