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()
Best Answer
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:
Executing the above should return the following:
You can sometimes get the Database ID from the following query, but not always:
The documentation states, regarding the value of the returned
dbid
field: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:
Unfortunately, you cannot get a
plan_handle
from eithersys.dm_exec_connections
orsys.dm_exec_sessions
. You can get it fromsys.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 selectdatabase_id
fromsys.dm_exec_sessions
or use the built-inDB_NAME()
andDB_ID()
functions (without passing in any parameters for them).UPDATE Addressing Additional Info in Question
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:
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, thentempdb
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:
master
and are marked as "system stored procedure" have a context of where they are being executed.CREATE PROCEDURE
, not necessarilytempdb
) but system functions (e.g.DB_NAME()
,DB_ID()
, etc) pick up the current DB.