SQL Server Performance

get the name of database

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Mar 2, 2010.

  1. shankbond New Member

    Hi,
    is it possible to get the name of the database through some t-sql
    actually I am creating a procedure where I have to get the name of database of calling procedure; is this possible
  2. FrankKalis Moderator

    Sure, just use DB_ID() DB_NAME()
  3. Adriaan New Member

    As far as I'm aware, DB_NAME() returns the name of the database where the sproc is defined.
    So if you're connected to DB1, and you're executing sproc2 from DB2, then sproc2's DB_NAME() will return DB2.
    You'll have to pass the db name as a parameter when calling the 'remote' procedure, in order for the remote procedure to know from where it's being called.
    I did find the slightly obscure @@REMSERVER which you can use to get the name of the calling server, when executing a remote procedure - but no corresponding function for a "calling database".
    I ran some tests with the 'remote' procedure getting the database_id from sys.dm_exec_requests for sessionid = @@SPID, but it was always referring to the db for the 'remote' procedure.
  4. FrankKalis Moderator

    Ah, I missed that bit. [:(]
    To the best of my knowledge this is not possible. Only way would be to add another parameter to the procedure and pass the calling database name in.
  5. Adriaan New Member

    I just remembered another back-handed way of doing this - not recommended, but anyway ...
    If you create a stored procedures in the master database, and the name starts with sp_, then you can call it from all databases, and it is executed in the context of the calling database. And so in that setup DB_NAME() does return the calling database.
    Not sure if we should encourage adding sp_ sprocs to master, though.
    [EDIT]"you can call it from all databases, and it is executed in the context of the calling database"
    But you have to call it without the database prefix for master, otherwise DB_NAME() will just return "master".
  6. shankbond New Member

    Thanks Guys, it helped

Share This Page