Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
It would be possible via extended events but only as a novelty really. Not something for serious production use. Even if you know the database name how would you use it anyway? Have everything in dynamic sql with a USE xyz; preceding it? –  Martin Smith Dec 12 '12 at 21:55
    
Honestly I can't say I have an solid case for why this would be necessary. I found it very interesting and if I do figure it out I will put it in two handy little procs I use in my sandbox dev database: One that gets the full name of an object given the shortest recognizable fragment of the name (such as in my first example in the question), and another that kills objects by using the other function to get the full name and also uses type of the identified object to generate a DROP statement. –  Joshua Honig Dec 12 '12 at 22:27
    
I would use SQLCLR for the ultimate purpose described above (you might like to edit that information into your question). See my answer to your other question –  Paul White Dec 12 '12 at 23:49
    
@SQLKiwi Question updated accordingly. Thanks for the other answer, too. I already have a variety of CLR functions for string manipulation, so this should be a natural next step for me. –  Joshua Honig Dec 13 '12 at 13:36

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.