Sql-server – Can a stored procedure reference the database in which it is stored

sql serverstored-procedures

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

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.