SQLCoffee - How to install SQL Server 2014

Server name in SQL Server

Server / April 23, 2017

I'm not sure what you were exactly asking. As you are writing this procedure for an Auditing need I guess you're asking how do you get the current database name when the Stored Procedure exists in another database. e.g.

USE DATABASE1 GO CREATE PROC spGetContext AS SELECT DB_NAME GO USE DATABASE2 GO EXEC DATABASE1..spGetContext /* RETURNS 'DATABASE1' not 'DATABASE2' */

This is the correct behaviour, but not always what you're looking for. To get round this you need to create the SP in the Master database and mark the procedure as a System Procedure. The method of doing this differs between SQL Server versions but here's the method for SQL Server 2005 (it is possible to do in 2000 with the master.dbo.sp_MS_upd_sysobj_category function).

USE MASTER /* You must begin function name with sp_ */ CREATE FUNCTION sp_GetContext AS SELECT DB_NAME GO EXEC sys.sp_MS_marksystemobject sp_GetContext USE DATABASE2 /* Note - no need to reference master when calling SP */ EXEC sp_GetContext /* RETURNS 'DATABASE2' */

Source: stackoverflow.com