How can I query the name of the current SQL Server

问题:

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.

回答1:

You should be able to use:

SELECT SERVERPROPERTY ('InstanceName') 

回答2:

SELECT DB_NAME()

Returns the database name.

回答3:

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

回答4:

You can use DB_NAME() :

SELECT DB_NAME()

回答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

回答6:

SELECT DB_NAME() AS DatabaseName

回答7:

simply use:

select @@servicename