如何返回在不同数据库中调用存储过程的远程数据库名称? [英] How to return the remote database name which is calling a stored proc in a different database?

问题描述

我在一个 SQL Server 2008 R2 上有许多不同的数据库.为了论证起见，我们称它们为 DB_A、DB_B 和 DB_C.我被要求开发为将存在于 DB_A 上的存储过程.这个存储过程将用于删除和创建索引，并在 DB_A 上的表中存储一些关于索引的额外信息.当从 DB_C 或 DB_C 调用此存储过程时，它将能够在调用数据库上删除和创建索引，但将有关索引的额外信息存储在 DB_A 上的表中.

I have a number of different databases on a single SQL Server 2008 R2. For arguments sake, let's call them DB_A, DB_B, and DB_C. I've been asked to develop as stored proc that will live on DB_A. This stored proc will be used to drop and create indexes, and also store some extra information about the index in a table on DB_A. When this stored proc is called from DB_C or DB_C, it will be able to drop and create indexes on the calling database, but store the extra information about the index in the table on DB_A.

这是我想要做的:我希望存储过程能够获取调用数据库的名称，而不必请求数据库名称作为参数.

Here's what I would like to do: I would like the stored proc to be able to get the name of the calling database WITHOUT having to request the database name as a parameter.

这是一个简单的例子:

USE [DB_A]

CREATE PROC sp_WhatDatabaseAmICallingFrom
AS 
BEGIN
      DECLARE @calling_db NVARCHAR(128)
      SET @calling_db = DB_NAME()
      PRINT 'calling database: ' + @calling_db
END

当我在 DB_A 中执行存储过程时...

When I execute the stored procedure in DB_A ...

EXEC sp_WhatDatabaseAmICallingFrom

...它返回:调用数据库:DB_A"

当我在 DB_B 中执行存储过程时...

When I execute the stored procedure in DB_B ...

USE DB_B
GO

EXEC DB_A.dbo.sp_WhatDatabaseAmICallingFrom

...它返回:调用数据库:DB_A".

在阅读了各种 SQL Server 元数据函数之后，这正是它应该做的.但我想要的是更改代码，以便将 @calling_db 设置为调用数据库的名称，以便我的示例存储过程将打印:调用数据库:DB_B".

After reading up on various SQL Server Metadata Functions, this is exactly what it should do. But what I would like is to change the code so that it sets @calling_db to the name of calling database, so that my example stored proc would print: "calling database: DB_B".

不幸的是，我找不到任何可以执行此操作的元数据函数.关于如何做到这一点的任何想法?

Unfortunately, I can't find any Metadata Functions that can do this. Any ideas on how this can be done?

推荐答案

要使 SP 在当前连接的上下文中运行，您需要在 master 数据库和上创建 SP使其成为系统对象.

To make SP run in the context of current connection you need create your SP on master database and make it a system object.

USE MASTER 
GO 

CREATE PROC sp_WhatDatabaseAmICallingFrom
AS 
BEGIN
      DECLARE @calling_db NVARCHAR(128)
      SET @calling_db = DB_NAME()
      PRINT 'calling database: ' + @calling_db
END
GO

EXEC sp_ms_marksystemobject 'sp_WhatDatabaseAmICallingFrom'
GO

检查它是如何工作的:

USE [DB_A]
GO

EXEC sp_WhatDatabaseAmICallingFrom
GO

这篇关于如何返回在不同数据库中调用存储过程的远程数据库名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！