SQL Server：通过变量更改当前数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9165513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: Change current database via variable
提问by Rick
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.
我正在使用 SQL Server 2008 并试图将当前数据库名称更改为变量中的一个。我通常使用 statment 明确地执行此操作USE myDatabaseName。问题出现是因为如果我正在运行一个脚本并且如果我不更改数据库名称，它会创建数据库中的所有表[master]。
I tried the following but doesn't seem to work as it keeps applying the rest of the create tablescodes to [master].
我尝试了以下但似乎不起作用，因为它不断将其余create tables代码应用于[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
回答by Martin Smith
Executing USE some_dbin dynamic SQL does work but unfortunately when the scope exits the database context gets changed back to what it was originally.
USE some_db在动态 SQL 中执行确实有效，但不幸的是，当范围退出时，数据库上下文会变回原来的状态。
You can use sqlcmdmode for this (enable this on the "Query" menu in Management Studio).
您可以sqlcmd为此使用模式（在 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
回答by Conrad Frix
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.
如果是这样，您可以将表创建或表修改部署到多个数据库，您可以将数据库创建和对象创建脚本分开，然后使用 .bat 文件按顺序运行它们input file -i。这使您能够将脚本之间的数据库从主数据库更改为新数据库。
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
回答by Conrad Frix
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
这可以通过动态sql来完成。使用一个变量来存储 dbname 并使用这个变量来构建一个 sql 字符串，如
SELECT @Sql ='SELECT fields FROM ' + @dbname +'.Table'
then use EXEC()or sp_executesqlto execute the sql string.
然后使用EXEC()或sp_executesql执行sql字符串。
