通过 T-SQL 访问当前正在执行的存储过程名称的名称? [英] Accessing the name of the currently executing stored procedure name via T-SQL?

问题描述

我需要记录存储过程名称以确定正在使用哪个存储过程.

I need to log the stored procedure name to determine which stored procedure are being used.

为了实现这一点，我在每个存储过程中嵌入了一个插入语句，我们必须记录它的使用情况.

To accomplish this I am embedding an insert statement in each of the stored procedures we have to log it's usage.

我可以在 INSERT 语句中硬编码 SP 名称，但我正在寻找一种优雅的方法来获取当前存储过程名称而不是硬编码，这主要是为了能够在之后搜索和删除相同的代码行项目.

I could hard code the SP name in the INSERT statement but I am looking for an elegant way to get the current stored procedure name rather than hard coding, this is primarily to be able to search for and remove identical lines of code after the project.

我有一个名为 tblUsed (ID INT, dateused date, sprocused varchar(50)) 的表，并计划在每个查询中进行插入.

I have a table called tblUsed (ID INT, dateused date, sprocused varchar(50)) and was planning to do an insert in each query.

INSERT INTO [stockist].[dbo].[tblUsed]
           ([objectName])
     VALUES
           (*procname*)

我只需要获取 proc 的名称即可使其正常工作.

I just need to get the name of the proc for this to work.

如果还有其他方法可以做到这一点，我很乐意听到.

If there are any other ways to accomplish this I would be happy to hear them.

提前致谢.

推荐答案

让我们从头开始为您解决这个问题.

Let's look at this from the ground up for you.

要获取存储过程的名称，您需要运行 OBJECT_NAME(object_id [, database_id ]) 元数据函数(更多信息 此处).当您在相关对象中运行此 T-SQL 时，您将不需要 database_id，因此您将运行的代码如下所示:

To get the name of the stored procedure you need to run the OBJECT_NAME(object_id [, database_id ]) metadata function(more info here). As you are running this T-SQL within the object in question, you won't need the database_id so the code you'll run will look something like this:

OBJECT_NAME(*object_id*)

要获取当前 T-SQL 模块的对象 ID，您需要使用 @@PROCID 元数据函数(更多信息 此处) 为您提供以下代码:

To get the object id for the current T-SQL module you will need to use the @@PROCID metadata function(more info here) giving you the following code:

OBJECT_NAME(@@PROCID)

在这种情况下，您的 INSERT 语句将如下所示:

In this case your INSERT statement will look like this:

INSERT INTO tblUsed (sprocused)
     VALUES (OBJECT_NAME(@@PROCID))

如果您使用多个架构，您可能需要使用 OBJECT_SCHEMA(object_id [, database_id ]) 元数据函数(更多信息 此处) 为您提供:

If you use multiple schema's you will probably need to be record which schema you are in using the OBJECT_SCHEMA(object_id [, database_id ]) metadata function(more info here) giving you this:

OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

在这种情况下，您的 INSERT 语句将如下所示:

In this case your INSERT statement will look like this:

INSERT INTO tblUsed (sprocused)
     VALUES (OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID))

实现此目的的另一种可能方法是通过使用 DMV(动态管理视图)在所有查询中不产生不必要的插入开销，这里是一个示例查询(来自 这个SO线程)

One other possible way to accomplish this without all the overhead of an unnecessary insert in all your queries is through the use of DMVs(Dynamic management views) here is a sample query (from this SO thread)

    SELECT sc.name
         , p.name 
      FROM sys.procedures AS p
INNER JOIN sys.schemas AS sc
        ON p.[schema_id] = sc.[schema_id]
LEFT OUTER JOIN sys.dm_exec_procedure_stats AS st
        ON p.[object_id] = st.[object_id]
     WHERE st.[object_id] IS NULL
  ORDER BY p.name;

注意:这只会为您提供上次重新启动 SQL 时的信息.

由于 这个问题你可能想确保你的过程没有在数据库中的任何其他地方被引用，你可以这样做:

As a result of this question you might want to ensure that none of your procs are referenced anywhere else in the db you can do this like so:

SELECT referencing_schema_name
     , referencing_entity_name
FROM sys.dm_sql_referencing_entities ('*schemaname.objectname*', 'OBJECT');

这篇关于通过 T-SQL 访问当前正在执行的存储过程名称的名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！