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?
Best Answer
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.