Find the Originating DB Name, Not the Current DB Name

Question

Find the Originating DB Name, Not the Current DB Name

fahey.jonathan

Hall of Fame

Points: 3617
More actions
September 17, 2019 at 8:57 pm

#3680861
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?

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

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

ScottPletcher SSC Guru Points: 100949 More actions · Answer 1

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) A socialist is someone who will give you the shirt off *someone else's* back.

heb1014 Hall of Fame Points: 3790 More actions · Answer 2

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.