Accessing the name of the currently executing stor

问题:

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.

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.

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*)

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.

Thank you in advance.

回答1:

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

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*)

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)

In this case your INSERT statement will look like this:

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

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)

In this case your INSERT statement will look like this:

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

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;

NB: This will only give you the information from the last time SQL was restarted.

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');