8

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?

9

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.

12

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.

0

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.

  • 1
    There's an even easier way. exec OtherDatabase.sys.sp_executesql N'select db_name()' – David Browne - Microsoft Aug 3 '18 at 18:16
  • Upvoted your comment since yours is even more terse – Derreck Dean Aug 3 '18 at 20:24
  • @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. – Mister Magoo Aug 3 '18 at 23:49
  • Well he's got the other database specified dynamically. If you don't need that, then it's simpler to just call directly. – David Browne - Microsoft Aug 3 '18 at 23:52
  • 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. – Derreck Dean Aug 7 '18 at 12:49
0

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
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.