In SQL Server, is it possible to get the DB_ID from the context from farther up the call stack?
My goal is to create some handy (and admittedly hacky) utility functions in a dev sandbox database that make it easy and concise to get the fully qualified names of objects given their short or fragmented names, and additionally to delete objects using the same short name. These utility functions would be in a single utility database but called from other databases on the same server.
From what I can see from testing:
ORIGINAL_DB_NAME()as intended returns whatever was in the connection string, not the current context (set byUSE [dbname]).- When called in a function
DB_NAME()returns the name of the database where that function is defined. Another way of saying this is that the context inside a function or stored procedure is that of the database in which it is defined
I know the engine keeps track of each of the database contexts up and down the call stack (see below for proof). So is there any way to access this information?
I want to be able to find and operate on objects in the context of the caller's database, even though the executing code is not in the same database. For example:
use SomeDB
EXEC util.dbo.frobulate_table 'my_table'
I know I can just do
EXEC util.dbo.frobulate_table 'SomeDB.dbo.my_table'
But I'm just really curious if it is possible to query the call stack in this way.
Update / note
I did read and download the code from Gabriel McAdams' blog. This provides a record of the calling procedure ID up and down the stack but still assumes everything is in the same database.
Proof the SQL Server remembers DB context up and down call stack
Example: On a dev server with databases TestDB1 and TestDB2
use TestDB1
GO
CREATE FUNCTION dbo.ECHO_DB_NAME() RETURNS nvarchar(128) BEGIN RETURN DB_NAME() END
GO
use TestDB2
GO
CREATE PROCEDURE dbo.ECHO_STACK AS
BEGIN
DECLARE @name nvarchar(128)
SET @name = DB_NAME()
PRINT 'Before, DB_NAME inside dbo.ECHO_STACK : ' + @name
SET @name = TestDB1.dbo.ECHO_DB_NAME()
PRINT 'TestDB1.dbo.ECHO_DB_NAME returned : ' + @name
SET @name = DB_NAME()
PRINT 'After, DB_NAME inside dbo.ECHO_STACK : ' + @name
END
GO
use master
SELECT DB_NAME() -- Returns 'master'
EXEC TestDB2.dbo.ECHO_STACK
The ECHO_STACK proc prints:
Before, DB_NAME inside dbo.ECHO_STACK : TestDB2
TestDB1.dbo.ECHO_DB_NAME returned : TestDB1
After, DB_NAME inside dbo.ECHO_STACK : TestDB2