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

how are you running the script?

Conrad Frix
– Conrad Frix

2012-02-06 18:52:40 +00:00
Commented Feb 6, 2012 at 18:52 — Conrad Frix
– Conrad Frix, Commented Feb 6, 2012 at 18:52
As a new query at the server level.

Rick
– Rick

2012-02-06 18:57:51 +00:00
Commented Feb 6, 2012 at 18:57 — Rick
– Rick, Commented Feb 6, 2012 at 18:57

Martin Smith · Accepted Answer · 2012-02-06 19:03:55Z

15

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

edited Feb 6, 2012 at 19:03

answered Feb 6, 2012 at 18:53

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Lamak Over a year ago

+1 - This is exactly (or, well, very close) to what I was answering. But you are really fast :-)

Rick Over a year ago

Is it possible to change from sqlcmd mode back to dynamic SQL in the same query?

Martin Smith Over a year ago

@Rick - Having sqlcmd mode enabled doesn't prevent you using any SQL constructs. The client tools do the variable substitution before the command is submitted to SQL Server.

Aaron Bertrand Over a year ago

@Rick, if you're happy with dynamic SQL, why not just do SET @sql = 'USE ' + @dbname + '; ... other dynamic SQL ...;';?

Arunas Bart Over a year ago

I would be more strict with USE $(dbname) - it substitutes variable directly as text when run with real sqlcmd, thus will through error. Correct way compatible both with sqlcmd mode and sqlcmd.exe is: USE [$(dbname)]

|

Conrad Frix · Accepted Answer · 2012-02-06 22:42:05Z

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

Dervall · Accepted Answer · 2012-02-06 20:50:38Z

0

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.

edited Feb 6, 2012 at 20:50

Dervall

5,7343 gold badges28 silver badges48 bronze badges

answered Feb 6, 2012 at 18:53

user1193035

1 Comment

MrCalvin Over a year ago

..the changes database only seem to apply to the SQL statement inside the parameter the exec / sp_executesql functions, not any following SQL statements

Collectives™ on Stack Overflow

SQL Server: Change current database via variable

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related