CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlssms
flipdoubt
flipdoubt

Reputation: 14465

How to query the name of the current SQL Server database instance?

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 instance 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 instance 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.

Upvotes: 41

Views: 86224

Answers (8)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can get the instance name of your current database as shown below:

SELECT @@SERVICENAME                   -- SQLEXPRESS
SELECT SERVERPROPERTY ('InstanceName') -- SQLEXPRESS

Upvotes: 0

kevin
kevin

Reputation: 486

SELECT
 @@servername AS 'Server Name' -- The database server's machine name
,@@servicename AS 'Instance Name' -- e.g.: MSSQLSERVER
,DB_NAME() AS 'Database Name'
,HOST_NAME() AS 'Host Name' -- The database client's machine name

Upvotes: 46

nad
nad

Reputation: 49

simply use: 

select @@servicename

Upvotes: 4

Giacomo Degli Esposti
Giacomo Degli Esposti

Reputation: 2452

You can use DB_NAME() :

SELECT DB_NAME()

Upvotes: 6

anonymous
anonymous

Reputation: 

SELECT DB_NAME() AS DatabaseName

Upvotes: 4

Dana
Dana

Reputation: 32997

SELECT DB_NAME()

Returns the database name.

Upvotes: 51

Gthompson83
Gthompson83

Reputation: 1099

You should be able to use:

SELECT SERVERPROPERTY ('InstanceName')

Upvotes: 1

Ollie
Ollie

Reputation: 885

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

Upvotes: 5

Related Questions