How to Dynamically change the database using TSQL

Question

I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL:

EXEC sys.sp_executesql N'USE db1 ' ;

It executes successfully however the database context of SSMS doesn't change.

I've tried a slight modification to the above like so

DECLARE @sql NVARCHAR(100) DECLARE @db NVARCHAR(50)
SET @db = N'db1' SET @sql = N'Use ' + @db
EXEC sp_executesql @sql

Again, it executes successfully, but the database doesn't change.

You cannot change the context within an sp_executesql for the session you are using in the SSMS. The context is only valid during your dynamic SQL session - not for the SSMS session. — Lothar Kraner, Sep 04 '17 at 11:14

score 18 · Accepted Answer · edited Jun 15 '20 at 09:05

SSMS WILL NOT, I REPEAT, WILL NOT SWITCH TO THE CONTEXT OF A USE COMMAND YOU RUN IN DYNAMIC SQL.

If the ultimate goal is to execute some other dynamic SQL inside the chosen database, this is easy enough:

DECLARE @db sysname = N'db1';

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'SELECT DB_NAME();';

EXEC @exec @sql;

If you need to pass parameters, no problem:

DECLARE @db sysname = N'db1', @i int = 1;

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'SELECT DB_NAME(), @i;';

EXEC @exec @sql, N'@i int', @i;

If the goal is to execute some static SQL inside the chosen database, maybe you should consider storing that static SQL in a stored procedure in each database, and calling it dynamically like this:

DECLARE @db sysname = N'db1';

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'EXEC dbo.procedurename;';

EXEC @exec @sql;

And hopefully the ultimate goal is not to run all this code in SSMS just so that SSMS is now in the context of @db... Daniel would really like it if I state explicitly that this is not possible, as @Lothar's comment also stated.

This is great, thanks Aaron Bertrand. And no, the ultimate goal isn't to run all this code in SSMS just so that SSMS is now in the context of @db — Mazhar, Sep 05 '17 at 15:30

score 3 · Answer 2 · answered Sep 04 '17 at 11:19

The dynamicSQL is not actually executed specifically in-line with the rest of your code its a separate entity (even though it is ran as if it is in-line

If you run the code: SET @sql = N'Use ' + @db + '; select DB_NAME(); select @@spid' in palce of your current set you'll notice the results that come back indicate that you have moved the active database, but you are still running under the same connection.

If you want to change the in-line database selection the best way is to do something like this:

IF @db = 'db1'
    USE db1
ELSE IF @db = 'db2'
    USE db2

Its not nice or clean and requires two lines per potential database but it'll get the job done (don't run it in dynamic SQL or you'll still end up with the same issue of the main thread not being changed)

Note though that using USE commands is prohibited in procedures / functions

score -2 · Answer 3 · answered Mar 25 '21 at 15:36

There is undocumented sp_MSforeachdb procedure:

DECLARE @TSQL nvarchar(2000)

SET @TSQL = '
IF ''?'' like ''mydbset-%''
BEGIN
   USE [?];
   PRINT ''********** database: [?] ************''
   select count(*) from information_schema.tables; -- ANY SQL or SP Call in database
END
'
-- Executing TSQL for each database
EXEC sp_MSforeachdb @TSQL

How to Dynamically change the database using TSQL

3 Answers3

SSMS WILL NOT, I REPEAT, WILL NOT SWITCH TO THE CONTEXT OF A USE COMMAND YOU RUN IN DYNAMIC SQL.

Linked