SQL Server:通过变量更改当前数据库 [英] SQL Server: Change current database via variable

问题描述

我正在使用 SQL Server 2008 并试图将当前数据库名称更改为变量中的一个.我通常使用语句 USE myDatabaseName 明确地执行此操作.出现这个问题是因为如果我正在运行一个脚本并且如果我不更改数据库名称，它会在 [master] 数据库中创建所有表.

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.

我尝试了以下操作，但似乎不起作用，因为它不断将其余的 create tables 代码应用到 [master].

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

推荐答案

在动态 SQL 中执行 USE some_db 确实有效，但不幸的是，当范围退出时，数据库上下文会变回原来的状态.

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.

您可以使用 sqlcmd 模式(在 Management Studio 的查询"菜单上启用此功能).

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

这篇关于SQL Server:通过变量更改当前数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！