41

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.

Improve this question
1

8 Answers 8

Reset to default
51 
SELECT DB_NAME()

Returns the database name.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

gives me master and not server\instance.
More relevant: SELECT HOST_NAME()
46 
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
Improve this answer

1 Comment

Add to that: ,HOST_NAME() AS 'Host Name'
6

You can use DB_NAME() :

SELECT DB_NAME()
Improve this answer

Comments

5

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

Improve this answer

Comments

4 
SELECT DB_NAME() AS DatabaseName
Improve this answer

Comments

4

simply use: 

select @@servicename
Improve this answer

1 Comment

This solution was already mentioned in the question and does not give the desired result
1

You should be able to use:

SELECT SERVERPROPERTY ('InstanceName')
Improve this answer

3 Comments

SELECT SERVERPROPERTY ('InstanceName') gives me a NULL
There are other properties, so you can also try SELECT SERVERPROPERTY('ServerName'), SERVERPROPERTY('MachineName') and so on. See MSDN documentation SERVERPROPERTY for a list of some of the properties that exist.
Flagged for moderators... doesn't work as an answer in 2019... Correct answer is SELECT DB_NAME() which has 46 points (46th is mine).
0

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

SELECT @@SERVICENAME                   -- SQLEXPRESS
SELECT SERVERPROPERTY ('InstanceName') -- SQLEXPRESS
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.