CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Rick
Rick

Reputation: 2308

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

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

Upvotes: 10

Views: 34445

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 3

user1193035
user1193035

Reputation:

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452947

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

Upvotes: 15

Related Questions