Sql-server – DB_ID context from farther up call stack

sql server

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 by USE [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

Best Answer

You cannot accomplish this with functions in a utility database. You can however create utility procedures in the master database, mark them as system objects, and call them from the context of any database on your system. An article with a good example can be found at this location.

Related Question