Can I get the dbname of the active database from a stored procedure in another database?

• Scott In Sydney

  Hall of Fame

  Points: 3331

  More actions

  November 8, 2017 at 11:11 pm

  #340434

  Create this SP in some database, in my case "Tools"

  USE Tools
GO

  CREATE PROCEDURE [dbo].[foo]
AS
BEGIN
  SELECT DB_NAME() [DB_NAME], DB_ID() [DB_ID]
END
GO

  EXEC foo
EXEC dbo.foo
EXEC Tools.dbo.foo

  So far so good.

  Now execute this code:

  USE SomeOtherDatabase
GO

  EXEC Tools.dbo.foo

  I want to return "SomeOtherDatabase".

  Is this possible?  Or am I confused about what constitutes the "current database"?

  (This is related to https://www.sqlservercentral.com/Forums/1905010/Where-should-I-save-stored-procedures-and-user-defined-functions-for-use-in-all-databases.  I'm still trying to store my system wide SP's in Tools or Util instead of master...)

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  November 9, 2017 at 8:23 am

  #1967392

  Scott In Sydney - Wednesday, November 8, 2017 11:11 PM

  Create this SP in some database, in my case "Tools"

  USE Tools
GO

  CREATE PROCEDURE [dbo].[foo]
AS
BEGIN
  SELECT DB_NAME() [DB_NAME], DB_ID() [DB_ID]
END
GO

  EXEC foo
EXEC dbo.foo
EXEC Tools.dbo.foo

  So far so good.

  Now execute this code:

  USE SomeOtherDatabase
GO

  EXEC Tools.dbo.foo

  I want to return "SomeOtherDatabase".

  Is this possible?  Or am I confused about what constitutes the "current database"?

  (This is related to https://www.sqlservercentral.com/Forums/1905010/Where-should-I-save-stored-procedures-and-user-defined-functions-for-use-in-all-databases.  I'm still trying to store my system wide SP's in Tools or Util instead of master...)

  It won't work doing it that way.
  If you were to create a stored procedure in master that would be available everywhere by marking it as a system object and prefixing it with sp_ , in that case if you did

  USE SomeOtherDatabase
  exec sp_foo - this would return SomeOtherDatabase
  exec master.dbo.sp_foo - this would return master

  This article walks you through the same type of thing, with additional explanations:
  SQL Server – Marking a stored procedure as system object

  Sue