SQL Server: Change current database via variable

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].

  • Normalizing an extremely big table
  • Append table to an existing one: SQL Server
  • Convert bit type to Yes or No by query Sql Server 2005
  • Configure Apache to use SQL Server authentication
  • Azure database name length
  • SQL Server transactions: insert causes locks?
  • 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
    

    3 Solutions collect form web for “SQL Server: Change current database via variable”

    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
    

    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 
    

    This can be done by means of dynamic sql. use a variable to store dbname and use this variable to build a sql string like

    SELECT @Sql ='SELECT fields FROM ' + @dbname +'.Table'
    

    then use EXEC() or sp_executesql to execute the sql string.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.