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

sql, sql-server, stored-procedures

Solution

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

Problem

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?

Original source