I feel that it is wise move for a script to actually check itself that the database connection that is being used, is infact operating in the intended database.

Occasionally, in the past, I have run scripts on servers where a database was expected to be - except it was not.

Or, the database in the USE statement was not accessible and so the database context had not been switched.

Both of these scenarios will probably result in commands being run in the wrong database, which in turn will either result in more errors or, worse, a whole heap of objects being created in the wrong database, usually master.

So, now I place a simple check after each USE statement, that will verify that the script is operating in the correct database and if not abort.

I've included the script here for reference.

USE SomeDatabase;
GO

IF (DB_NAME() <> 'SomeDatabase')

BEGIN

PRINT 'INCORRECT DB';

RAISERROR ('Cannot run without being in SQLCMD mode',20,1) WITH LOG;

--RETURN;

END

ELSE

BEGIN

PRINT 'CORRECT DB';

END

GO

Other times I get scripts that must be run using SQLCMD mode.

SSMS will usually be in TSQL mode by default (although this is possible to change).

If a SQLCMD script is run when SSMS is in TSQL mode then the SQLCMD parts will throw errors, but the TSQL parts of the script will run fine.

This is probably not what you want, especially if SQLCMD is being used to create a database and then the context is being switched to that DB.

Fortunatly, template explorer provides a script that will check if the connection is indeed a SQLCMD one and terminate it if it is simply a TSQL connection.

This template is provided under the "SQLCMD" folder in SSMS.

I've provided it below just for reference.

if ('$(SqlCmdIsOn)'<>'Yes')
BEGIN
RAISERROR ('Cannot run without being in SQLCMD mode',20,1) with log;
END
ELSE
BEGIN
PRINT('SQLCMD Mode is set to on');
END
go

Have a good day

Martin.