SQL Server: Change current database via variable

Question

I'm using SQL Server 2008 and am trying to change the current database name to one in a variable. I normally do this explicitly with the statment USE myDatabaseName. The question arises because if I am running a script and if I don't change the database name it creates all the tables in the [master] database.

I tried the following but doesn't seem to work as it keeps applying the rest of the create tables codes to [master].

DECLARE @dbName CHAR(50)
DECLARE @SqlQuery varchar(50)
SET @dbName = 'MyNewDatabaseName'

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = @dbName)
BEGIN
    SELECT @SqlQuery = 'CREATE DATABASE ' + @dbName + 'COLLATE SQL_Latin1_General_CP1_CI_AS'
    EXEC(@SqlQuery) 
END

Select @SqlQuery = 'Use ' + @dbName
EXEC(@SqlQuery)
go

Answer 1

Executing USE some_db in dynamic SQL does work but unfortunately when the scope exits the database context gets changed back to what it was originally.

You can use sqlcmd mode for this (enable this on the "Query" menu in Management Studio).

:setvar dbname "MyNewDatabaseName" 

IF DB_ID('$(dbname)') IS NULL
    BEGIN

    DECLARE @SqlQuery NVARCHAR(1000);
    SET @SqlQuery = N'CREATE DATABASE ' + QUOTENAME('$(dbname)') + ' 
            COLLATE SQL_Latin1_General_CP1_CI_AS'
    EXEC(@SqlQuery) 

    END

GO

USE $(dbname)

GO

Answer 2

Just to add Martin Smith's answer,

If this is so you can deploy your Table creation or Table modification to multiple database you can separate your Database Creation and Object creation scripts, and then run them in sequence using a bat file using the input file -i. This enables you to change databases between scripts from master to the new database.

then your batch file might

 sqlcmd -S server\Instance -E -i createdatabase.sql 
 sqlcmd -S server\Instance -E -d MyNewDatabaseName -i CreateTables.sql 

Typically however I've only needed to do this when I was deploying changes to multiple databases (don't ask why) e.g.

 sqlcmd -S server\Instance -E -d OneDatabase -i CreateTables.sql 
 sqlcmd -S server\Instance -E -d AnotherDatabase -i CreateTables.sql