Say we have a database Utils
with a procedure MyUtil
and we do:
USE MyDatabase
GO
EXEC Utils.dbo.MyUtil
GO
then as per the original question we would like Utils.dbo.MyUtil
to perform some utility operations inside MyDatabase
, not inside Utils
.
There seems to be no direct way to do exactly this. Accessing DB_NAME()
, sys.sysprocesses
or sys.dm_exec_sessions
within Utils.dbo.MyUtil
will always identify the current database as Utils
regardless of where Utils.dbo.MyUtil
was called from. On the other hand syslogins
and sys.sql_logins
return the login database, not the current database (e.g. as selected with USE xyz
).
The problem is not that Utils.dbo.MyUtil
can't do it's work, it's that it can't automatically work out where to do its work (i.e. it can't tell where it was called from), as we'd ideally like it do.
One option (still less than ideal, because it involves modifying master
; this builds on and adds to the answers by @RandiVertongen and @DaniloBraga) is to provide a thin wrapper procedure in the master
database:
USE master
GO
CREATE PROCEDURE sp_MyUtil
{...other params}
AS
BEGIN
SET NOCOUNT ON;
DECLARE @default_db_name sysname = DB_NAME()
EXEC Utils.dbo.MyUtil {...other params,} @default_db_name = @default_db_name
END
GO
USE Utils
GO
ALTER PROCEDURE MyUtil
{...other params,}
@default_db_name sysname = null
AS
BEGIN
SET NOCOUNT ON;
{...do stuff which uses @default_db_name to identify the calling DB, this is set correctly and passed automatically when sp_MyUtil is used}
END
GO
No you can call:
USE MyDatabase
GO
EXEC sp_MyUtil
GO
this ends up executing Utils.dbo.MyUtil
, but now with enough information available to it for it to do its work in MyDatabase
.
Bear in mind the strong recommendation (for security reasons) never to install modifications to the master
database on a production server. Because these master
procedures are only a convenience to enable the user to type less, you could always install the Utils
database without the master
wrapper procedures on the production server... if you needed to.
IF @dbname IS NULL then @dbname = (SELECT dbname())
or you mean the calling procedure itself? – Jacob H Mar 14 '19 at 18:03