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

Question

This is a bit like a chicken or egg query, but can someone come up with a query that can return the name of the current database instance in which the query is being executed? Believe me, when I say, I understand the paradox: why do you need to know the name of the database instance if you are already connected to execute the query? Auditing in an environment with multiple databases.

I looked through all @@ globals in Books Online. " SELECT @@servername " is close, but I need the name of the database instance, not the server.

Answer 1

You should be able to use:

 SELECT SERVERPROPERTY ('InstanceName') 

Answer 2

 SELECT DB_NAME() 

Gets the name of the database.

Answer 3

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

Answer 4

You can use DB_NAME () :

 SELECT DB_NAME() 

Answer 5

 SELECT DB_NAME() AS DatabaseName 

Answer 6

I'm not sure what you asked for sure. When you write this procedure for audit, I think you are asking how to get the current database name when the Stored Procedure exists in another database. eg.

 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 behavior, but not always what you are looking for. To get around this, you need to create an SP in the Master database and mark the procedure as a System procedure. The way this is done is different from the versions of SQL Server, but the SQL Server 2005 method is used here (this can be done in 2000 using the function master.dbo.sp_MS_upd_sysobj_category ).

 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

Answer 7

just use:

 select @@servicename