how to find the database context of the last sql statement?

Question

I would like to find out from the code below, what was the name of the database where the last command was run.

In this case I am looking for my_other_database.

his there any way of finding this? Is there any DMV that I should be looking at for this info?

Also, what if the update was wrapped in a dynamic sql, would it be possible to track it then?

use my_database
go

begin tran t1

UPDATE my_other_database.REF.applicationReference
SET uploadPaperReferences = 0
WHERE REFERENCEID IN (
69361,
69690,
69354,
69358,
69362,
69732,
69863,
70187
)

commit tran t1

what am I trying to accomplish? something like on the example below at the end I print out the name of the server and the name of the database that I am on:

but on the example below, although I was technically inside database cola, the statement was apcore.upl.applicationDocument, apcore is another database.

is there a way to print out apcore, instead of cola?

SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN T1

PRINT ''
PRINT 'AFTER BEGIN TRAN'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT '@@SERVERNAME: ' + CAST(@@SERVERNAME AS VARCHAR) + CHAR(10) + 'Databasename: ' + DB_NAME()
PRINT ''


DECLARE @row INT

USE COLA
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

            update t
            set documentstateid = 5
             from apcore.upl.applicationDocument t
            where applicationid in (
                                    319761
                                    ,320455
                                    ,333433
                                    ,351642
                                    ,371539
                                    ,372508
                                    )

             and documentStateId not in (1,5)

SELECT @row = @@ROWCOUNT

PRINT ''
PRINT 'AFTER RUNNING THE UPDATE(s)'
PRINT ''
PRINT 'The number of rows updated:' + SPACE(1) + CAST(@row as varchar)
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()


COMMIT TRAN T1

PRINT ''
PRINT 'AFTER COMMIT'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()

Answer 1

Ad hoc queries execute in the Database that was either logged into, or mostly recently changed to via a USE statement.

Queries running within a Stored Procedure or Function execute in the Database in which the module they are contained in exists.

For example:

USE [tempdb];
-- DROP PROCEDURE dbo.CurrentDatabaseTest;
GO
CREATE PROCEDURE dbo.CurrentDatabaseTest
AS
SET NOCOUNT ON;

SELECT db.[database_id], db.[name]
FROM sys.dm_exec_sessions es
INNER JOIN sys.databases db
        ON db.[database_id] = es.[database_id]
WHERE es.[session_id] = @@SPID;
GO

USE [model];

SELECT db.[database_id], db.[name]
FROM sys.dm_exec_sessions es
INNER JOIN sys.databases db
        ON db.[database_id] = es.[database_id]
WHERE es.[session_id] = @@SPID;

EXEC tempdb.dbo.CurrentDatabaseTest;
GO

Executing the above should return the following:

database_id    name
3              model


database_id    name
2              tempdb

You can sometimes get the Database ID from the following query, but not always:

SELECT txt.*
FROM   sys.dm_exec_connections con
OUTER APPLY  sys.dm_exec_sql_text(con.[most_recent_sql_handle]) txt
--WHERE  con.[session_id] = @@SPID;

The documentation states, regarding the value of the returned dbid field:

For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

However, my testing shows that it appears to be NULL if the last statement was ad hoc.

Ah, but I just saw a note stating:

dbid cannot be determined from sql_handle for ad hoc queries. To determine dbid for ad hoc queries, use plan_handle instead.

Unfortunately, you cannot get a plan_handle from either sys.dm_exec_connections or sys.dm_exec_sessions. You can get it from sys.dm_exec_requests, but rows are only there when a session is executing a query. Once the query is over, that row is not in that DMV anymore. And if you use that DMV to get the value for your current session, then it will always be the Database that you are currently in since the query will be the one you are executing to find the Database, in which case you might as well just select database_id from sys.dm_exec_sessions or use the built-in DB_NAME() and DB_ID() functions (without passing in any parameters for them).

---

UPDATE Addressing Additional Info in Question

[i]n the example below, although I was technically inside database cola, the statement was apcore.upl.applicationDocument, apcore is another database.

Yes, those are two different databases, but I think you are misunderstanding what is going on here. That statement referenced a single object. But consider a multi-object query:

        update t
        set documentstateid = 5
         from apcore.upl.applicationDocument t
           INNER JOIN other_db.dbo.some_table st
                   ON st.column = t.column
        where applicationid in (...

Now there are two databases referenced, neither one of which you are in.

If there is a trigger on that table, or if the query uses the OUTPUT clause, or if you have Snapshot Isolation enabled, then tempdb is also involved.

The only context that actually exists is the DB that you are currently in.

And in terms of modules (stored procedures, triggers, functions, and views), the context is the DB in which they reside, regardless of where they are called from.

There are two exceptions for modules, however:

1. Stored procedures that reside in master and are marked as "system stored procedure" have a context of where they are being executed.
2. Temporary stored procedures are an odd case in that the queries in them execute in the DB in which they were created (the current DB at the time of CREATE PROCEDURE, not necessarily tempdb) but system functions (e.g. DB_NAME(), DB_ID(), etc) pick up the current DB.

Answer 2

The context of this script is my_database. It's just that the query references objects in a different database. Consider a scenario where a single query uses objects in multiple databases. Which database would you want returned?

What are you trying to accomplish?