如何查询当前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.

我查看了联机丛书中的所有@@ globals。 “SELECT @@ servername”接近，但我想要数据库实例的名称而不是服务器。

7 个解决方案

#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() :

您可以使用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).

这是正确的行为，但并不总是你想要的。为了解决这个问题，您需要在Master数据库中创建SP，并将该过程标记为系统过程。执行此操作的方法在SQL Server版本之间有所不同，但这里是SQL Server 2005的方法（可以在2000年使用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

希望这是你想要的

#6

SELECT DB_NAME() AS DatabaseName

#7

simply use:

只需使用：

select @@servicename

#1