Databases
StackOverflow2013
Postsdbo
POHow to return the remote database name which is calling a stored proc in a different database?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to return the remote database name which is calling a stored proc in a different database?
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>Here is a simple example:</p> <pre><code>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 </code></pre> <p>When I execute the stored procedure in DB_A ...</p> <pre><code>EXEC sp_WhatDatabaseAmICallingFrom </code></pre> <p>...it returns: "<strong>calling database: DB_A</strong>"</p> <p>When I execute the stored procedure in DB_B ...</p> <pre><code>USE DB_B GO EXEC DB_A.dbo.sp_WhatDatabaseAmICallingFrom </code></pre> <p>...it returns: "<strong>calling database: DB_A</strong>".</p> <p>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: "<strong>calling database: DB_B</strong>".</p> <p>Unfortunately, I can't find any Metadata Functions that can do this. Any ideas on how this can be done?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSteve Mazer
    1. USSteve Mazer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to return the remote database name which is calling a stored proc in a different database?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to return the remote database name which is calling a stored proc in a different database?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to return the remote database name which is calling a stored proc in a different database?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COVery interesting question. Out of curiosity, is there a reason why you don't want to have the process that calls the sproc provide the dbname as a parameter (other than having to support a parameter and provide it)? One thought if there isn't a direct way to do this would be to make the parameter optional @CallingDB NVARCHAR(128) = NULL and check if the value is null. If so, default to use DB_NAME(). Of course, this means the invocation from other databases would need to provide the name...
      singulars
      1. POHow to return the remote database name which is calling a stored proc in a different database?
      1. USTim Lentine
    2. COThe only real reason I don't want to use a parameter is I think that there must be a way to do it, and I hate walking away from a challenge like this without finding a solution.
      singulars
      1. POHow to return the remote database name which is calling a stored proc in a different database?
      1. USSteve Mazer
    3. COFYI, the same question was [asked on the DBA site](http://dba.stackexchange.com/questions/30310/db-id-context-from-farther-up-call-stack), although it hasn't been answered. The suggestions made were to use extended events, SQL CLR or maintain your own call stack using [`CONTEXT_INFO`](http://msdn.microsoft.com/en-us/library/ms187768.aspx).
      singulars
      1. POHow to return the remote database name which is calling a stored proc in a different database?
      1. USPondlife
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload