How to return the remote database name which is calling a stored proc in a different database?

Question

I have a number of different databases on a single SQL Server 2008 R2. For arguments sake, let's call them DB_A, DB_B, and DB_C. I've been asked to develop as stored proc that will live on DB_A. This stored proc will be used to drop and create indexes, and also store some extra information about the index in a table on DB_A. When this stored proc is called from DB_C or DB_C, it will be able to drop and create indexes on the calling database, but store the extra information about the index in the table on DB_A.

Here's what I would like to do: I would like the stored proc to be able to get the name of the calling database WITHOUT having to request the database name as a parameter.

Here is a simple example:

USE [DB_A]

CREATE PROC sp_WhatDatabaseAmICallingFrom
AS 
BEGIN
      DECLARE @calling_db NVARCHAR(128)
      SET @calling_db = DB_NAME()
      PRINT 'calling database: ' + @calling_db
END

When I execute the stored procedure in DB_A ...

EXEC sp_WhatDatabaseAmICallingFrom

...it returns: "calling database: DB_A"

When I execute the stored procedure in DB_B ...

USE DB_B
GO

EXEC DB_A.dbo.sp_WhatDatabaseAmICallingFrom

...it returns: "calling database: DB_A".

After reading up on various SQL Server Metadata Functions, this is exactly what it should do. But what I would like is to change the code so that it sets @calling_db to the name of calling database, so that my example stored proc would print: "calling database: DB_B".

Unfortunately, I can't find any Metadata Functions that can do this. Any ideas on how this can be done?

Answer 1

To make SP run in the context of current connection you need create your SP on master database and make it a system object.

USE MASTER 
GO 

CREATE PROC sp_WhatDatabaseAmICallingFrom
AS 
BEGIN
      DECLARE @calling_db NVARCHAR(128)
      SET @calling_db = DB_NAME()
      PRINT 'calling database: ' + @calling_db
END
GO

EXEC sp_ms_marksystemobject 'sp_WhatDatabaseAmICallingFrom'
GO

Check how it works:

USE [DB_A]
GO

EXEC sp_WhatDatabaseAmICallingFrom
GO

Answer 2

I know this thread is quite old but I found something that at least helped me.

If you are using DB_A and are calling an stored procedure in DB_B like:

USE DB_A
EXEC db_b.dbo.sproc

The only way I have found to get the "calling database" id is to run a select against sys.dm_tran_locks inside the stored procedure. request_session_id should be your spid, resource_type should be DATABASE and request_owner_type should be SHARED_TRANSACTION_WORKSPACE. Such a shared lock always exist on a database for each connected session. The query would be something like:

SELECT resource_database_id FROM sys.dm_tran_locks WHERE request_session_id = @@SPID and resource_type = 'DATABASE' and request_owner_type = 'SHARED_TRANSACTION_WORKSPACE'

Although this requires the executing user to have at least VIEW SERVER STATE permissions on the server. In my case that was not a problem though..