Find the Originating DB Name, Not the Current DB Name

  • I am considering creating a "Tools" (or similar name) database to hold common procedures and functions so that they don't have to be created and maintained on every individual database.  I'm open to comments on whether that is a good idea or not, but that is not my question.  For functions that require knowledge of the originating database, it would be nice to be able to determine that value inside the procedure rather than having to pass in that value.  However, I cannot figure out how to do that.

    Here is what I have tried.  On my "Tools" database, I created some "proof of concept" and testing code:

    USETools
    GO

    CREATE-- DROP
    PROCjtfMyDB
    AS
    SELECT[MyDB sys.sysprocesses] = DB_NAME(dbid) FROM sys.sysprocesses WITH(NOLOCK) WHERE spid = @@SPID
    SELECT[MyDB sys.dm_exec_sessions] = DB_NAME(database_id) FROM sys.dm_exec_sessions WITH(NOLOCK) WHERE session_id = @@SPID
    GO

    CREATE-- DROP
    PROCjtfTestWho
    AS
    EXECsp_who2 @@SPID
    GO

    EXECTools.dbo.jtfMyDB
    EXECTools.dbo.jtfTestWho

    -- OUTPUT
    MyDB sys.sysprocesses
    -------------------------------------------------------------------------------------------------------
    Tools

    MyDB sys.dm_exec_sessions
    -------------------------------------------------------------------------------------------------------
    Tools

    SPID Status Login BlkBy DBName
    ----- ------------------------------ -------------------------------------------------- ----- ------ ...
    53 RUNNABLE DOMAINNAME\jonathan.fahey . Tools

    This correctly shows that my "current" database is "Tools", and all three versions produce the same result.  Now I change my "current" database to another working database, "LiveData".  When I call the procedures on "Tools" ...

    USELiveData
    GO

    EXECTools.dbo.jtfMyDB
    EXECTools.dbo.jtfTestWho

    ... I get the same results (I won't post duplicate results).  I have not changed my "current" database on my connection, but the results show that SQL Server thinks my current database is "Tools".

    I thought that maybe creating and calling a procedure on "LiveData" would give me different results, because I am explicitly calling a procedure on the "LiveData" database, which would ensure that my "current" database on the connection is "LiveData".

    USELiveData
    GO

    CREATE-- DROP
    PROCjtfLocalProc
    AS
    EXECTools.dbo.jtfMyDB
    EXECTools.dbo.jtfTestWho
    GO

    EXECLiveData.dbo.jtfLocalProc

    Again, all three methods - sys.sysprocesses, sys.dm_exec_sessions, and sp_who2 - say that my "current" database is "Tools", when clearly my SSMS is connected to "LiveData" and I am calling a procedure on "LiveData".

    I tried checking the system stored procedures on "master" to see how they work, but the ones I checked simply reference "db_name()", which indicates to me that SQL Server keeps the connection on the "current" database and simply finds the code to execute on "master" rather than on "LiveData", then executes it in the context of "LiveData".  But when I create procedures that reside on "Tools", SQL Server seems to change the "current" database to "Tools" when executing that code.  That makes sense because it allows us to procedures on other databases and have them return "local" results.  That is usually what I want, but not in this case.

    My question: Is there any way in the procedures on "Tools" to determine that the originating call came from "LiveData" without explicitly passing in the originating database name?

  • I've never been able to find a way to do this.  I ended up using CONTEXT_INFO() [*] or sp_set_session_context to pass the database_id to the called proc. [*] With provisions for how to pass levels of dbs too (i.e. procA calls db2.procB which in turn call db3.procC, etc.), which can also be done if needed.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I also do not know of a way to determine the originating database.

    I see value in having only a single copy of your tools objects in a tools database.  Your strategy would reduce the maintenance costs of ensuring all objects exist and are current in all databases.  However, I think this type of centralization would create a dependency between each of your databases and the tools database such that a database could not be restored over to a new server unless the tools database was also restored over.  Over time I have come to value not having dependencies between databases.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply