Switching between databases with dynamic SQL

Question

I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database.

Executing this in [test_db]:

declare @currentDB varchar(max)
declare @sql varchar(max)
set @currentDB = DB_NAME()
set @sql = 'use [' + @currentDB +']'
use master
exec(@sql)
select DB_NAME()

Returns [Master] as the current database name - if I put use [test_db] as a command, rather than dynamically, then it returns the correct name.

Is there a way to do this which will correctly switch between databases?

Answer 1

Session-level changes made in a sub-process (i.e. EXEC / sp_executesql) go away when that sub-process ends. This covers USE and SET statements as well as any local temporary tables created in that sub-process. Creation of global temporary tables will survive the sub-process, and so will modifications made to local temporary tables that exist prior to the sub-process starting, and any changes to CONTEXT_INFO (I believe).

So no, you cannot dynamically change the current database. If you need to do something like this, you will need to execute any subsequent statements that rely upon the new database context also within that Dynamic SQL.

Answer 2

Sure, there is a way - there's always a way...

If you declare variable and store in it the database and the procedure to run, you can exec it, with parameters.

Example

use tempdb;
select db_name();
declare @db sysname = 'master.sys.sp_executesql';
exec @db N'select db_name()';
set @db = 'msdb.sys.sp_executesql';
exec @db N'select db_name()';

It is trivial to then pass a query with parameters to be run in any database

declare @proc sysname, @sql nvarchar(max), @params nvarchar(max);
select 
 @proc = 'ssc.sys.sp_executesql'
, @sql = N'select top 10 name from sys.tables where name like @table order by name;'
, @params = N'@table sysname';
exec @proc @sql, @params, @table = 'Tally%'

I know this doesn't change database context in the main query, but wanted to demonstrate how you can conveniently work in another database in a safe parameterised way without too much bother.

Answer 3

Basing this on @Mister Magoo's answer...

CREATE PROCEDURE dbo.Infrastructure_ExecuteSQL
(
 @sql NVARCHAR(MAX),
 @dbname NVARCHAR(MAX) = NULL
)
AS BEGIN
 /*
 PURPOSE
 Runs SQL statements in this database or another database.
 You can use parameters.
 TEST
 EXEC dbo.Infrastructure_ExecuteSQL 'SELECT @@version, db_name();', 'master';
 REVISION HISTORY
 20180803 DKD
 Created
 */
 /* For testing.
 DECLARE @sql NVARCHAR(MAX) = 'SELECT @@version, db_name();';
 DECLARE @dbname NVARCHAR(MAX) = 'msdb';
 --*/
 DECLARE @proc NVARCHAR(MAX) = 'sys.sp_executeSQL';
 IF (@dbname IS NOT NULL) SET @proc = @dbname + '.' + @proc;
 EXEC @proc @sql;
END;

I have lots of maintenance-related uses for this.

• David Browne - Microsoft: There's an even easier way. exec OtherDatabase.sys.sp_executesql N'select db_name()'
• Derreck Dean: Upvoted your comment since yours is even more terse
• Mister Magoo: @DavidBrowne-Microsoft that's what Derreck is doing here, but with "OtherDatabase" passed as a parameter - isn't it? So they end up with OtherDatabase.sys.sp_executesql in the "proc" variable instead of hard-coded.
• David Browne - Microsoft: Well he's got the other database specified dynamically. If you don't need that, then it's simpler to just call directly.
• Derreck Dean: I am still using mine since I use this to loop through a specific set of related databases and perform actions on them such as indexing, backups, etc. using the Ola Hallengren scripts that I have baked into the 'master' database of my app (not the actual master db). It's good to know that I can call out to a specific database directly as in his comment.

Answer 4

This works too.

declare @Sql nvarchar(max),@DatabaseName varchar(128)
set @DatabaseName = 'TestDB'
set @Sql = N'
 declare @Sql nvarchar(max) = ''use ''+@DatabaseName
 set @Sql = @Sql +''
 select db_name()
 ''
exec (@Sql)
'
exec sp_executesql @Sql,N'@DatabaseName varchar(128)',@DatabaseName

Answer 5

Learning from the previous post I went a little deeper and impressed myself...

DECLARE @Debug BIT = 1
DECLARE @NameOfDb NVARCHAR(200) = DB_NAME()
DECLARE @tsql NVARCHAR(4000) = ''
 IF OBJECT_ID('Tempdb.dbo.#tbl001') IS NOT NULL DROP TABLE #tbl001
 CREATE TABLE #tbl001(
 NameOfDb VARCHAR(111))
 INSERT INTO #tbl001(NameOfDb)
 VALUES('db1'),('db2'),('db3'),('db4')
SET @tsql = N'
DECLARE @sql nvarchar(max) 
set @sql = N''
;WITH a AS (
 SELECT NumOf = COUNT(*),
 c.Field1,
 c.Field2,
 c.Field3
 FROM ''+@NameOfDb2+''.dbo.TBLname c
 WHERE Field3 = ''''TOP SECRET''''
 GROUP BY
 c.Field1,
 c.Field2,
 c.Field3
 HAVING COUNT(*)>1
)
SELECT a.NumOf, c.* 
FROM ''+@NameOfDb2+''.dbo.TBLname c
JOIN a ON c.Field1=a.Field1 AND c.Field2=a.Field2 AND c.Field3=a.Field3''
exec (@sql)
'
DECLARE SmplCrsr CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY FOR 
 SELECT * FROM #tbl001
OPEN SmplCrsr;
FETCH NEXT FROM SmplCrsr
 INTO @NameOfDb
WHILE @@Fetch_Status=0
 BEGIN
 IF (@Debug = 1) 
 BEGIN
 EXEC sys.sp_executesql @tsql,N'@NameOfDb2 varchar(111)',@NameOfDb
 END
 ELSE 
 BEGIN
 PRINT @tsql + '-- DEBUG OFF'
 END
 FETCH NEXT FROM SmplCrsr
 INTO @NameOfDb
 END
CLOSE SmplCrsr;
DEALLOCATE SmplCrsr;