How can I query the name of the current SQL Server database instance?

Question

It is a bit of a "chicken or egg" kind of query, but can someone dreamup a query that can return the name of the current database in which the query executes? Believe me when I say I understand the paradox: why do you need to know the name of the database if you're already connected to execute the query? Auditing in a multi-database environment.

I've looked at all the @@ globals in Books Online. "SELECT @@servername" comes close, but I want the name of the database instance rather than the server.

Answer 1

SELECT DB_NAME()

Answer 2

SELECT DB_NAME() AS DatabaseName

Answer 3

You should be able to use:

SELECT SERVERPROPERTY ('InstanceName')

Answer 4

You can use

SELECT DB_NAME() AS DataBaseName

to get the db name. Found on this site.

Answer 5

You can use DB_NAME :

select DB_NAME()

Answer 6

Look here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1418914&SiteID=1

Answer 7

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' */

Hope this is what you were looking for