EXEC 到 USE 数据库 [英] EXEC to USE Database

问题描述

我编写了一个脚本，用于生成数据库备份文件名、备份数据库，然后将其恢复为具有新数据库名的副本.该名称基于某些日期/时间数据.

I have written a script that generates a database backup file name, backs up the database, and then restores it as a copy with the new database name. The name is based on some date/time data.

然后我需要在脚本中USE那个数据库，然后禁用所有触发器.

I then need to USE that database, in the script, and then disable all triggers.

但是，这不起作用:

DECLARE @Use VARCHAR(50)
SET @Use = 'USE ' + @NewDatabaseName

EXEC(@Use)

手动运行 - 数据库不会被使用".

Running it manually - the database doesn't get 'USED'.

如何使用变量执行 USE 语句?

How can I execute the USE statement with a variable?

我也尝试过 sp_executesql 过程，结果相同.数据库没有改变.

I have tried the sp_executesql proc as well, with the same result. Database didn't change.

DECLARE @sqlCommand nvarchar(1000)
SET @sqlCommand = 'USE ' + @NewDatabaseName

EXECUTE sp_executesql @sqlCommand

看起来我可能需要进入 sqlcmd 模式?不过真的希望不要.

Looks like I might need to go into sqlcmd mode? Really hoping not to, though.

推荐答案

exec 和 execute_sql 都在各自的范围内运行.而数据库的变化只会影响自己的范围.所以你可以:

Both exec and execute_sql run in their own scope. And the change in database will only affect their own scope. So you could:

set @sql = 'use ' + quotename(@new_db_name) + '; disable trigger t1;'
exec (@sql)

据我所知，没有办法将当前作用域的数据库上下文更改为可变数据库名称.

As far as I know, there is no way to change the database context of the current scope to a variable database name.

这篇关于EXEC 到 USE 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！