Xjyuk

Suppose I have a stored procedure that is duplicated, with some modifications, in several databases. And I want to reference the database in which the stored procedure is stored, even if it is executed in another database.

Is there a way to retrieve the full path (..) or otherwise retrieve the database in which the stored procedure is stored, rather than the current database?

I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.

Just use one-part or two-part names in the stored procedure, and it will reference objects in the database containing the stored procedure. In particular,

For static SQL in a stored procedure:

• Unqualified object names will resolve relative to the schema containing the stored procedure.




• Two-part names will resolve relative to the database containing the stored procedure.

For dynamic SQL in a stored procedure:

• Unqualified object names will resolve relative to the default schema of the user identity running the stored procedure (by default, the caller).




• Two-part names will resolve relative to the database containing the stored procedure.

The db_name() function will return the name of the database containing the stored procedure in both cases.

Here is a quick example that I put together showing common functions used to get close to what you are looking for.

/** Create a procedure in master to demonstrate
 DB_NAME()
 OBJECT_SCHEMA_NAME()
 OBJECT_NAME()
 @@PROCID
**/
USE [master]
GO

CREATE OR ALTER PROCEDURE dbo.uspTestMe
AS
BEGIN

 PRINT 'Database: ' + DB_NAME()
 PRINT 'Schema Name: ' + OBJECT_SCHEMA_NAME(@@PROCID)
 PRINT 'Procedure Name: ' + OBJECT_NAME(@@PROCID)

END

GO

/** CHANGE Context to TempDB
 Execute procedure in master
 **/
USE [tempdb]
GO

EXEC master.dbo.uspTestMe 

GO

/** Cleanup in master **/
USE [master]
GO

DROP PROCEDURE IF EXISTS dbo.uspTestMe