Get the name of the executing SQL Stored Procedure, UDF, or trigger inside the object

On SQL Serer using T-SQL how do you determine the name of the object, such as stored procedure or UDF, that is executing from inside the object while it is executing.

FROM BOL:

@@PROCID (Transact-SQL)

Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider.

couple this with the Object_Name() function and Wah-Lah!

Try the following inside a stored procedure:

SELECT
	  SCHEMA_NAME(SO.schema_id) AS SchemaName
	, OBJECT_NAME(@@PROCID) AS ObjName
	, @@PROCID AS ProcID
FROM	
	sys.objects so
WHERE
	SO.object_id = @@PROCID

If you don't need the schema save the trip to the sys.objects table.

This code even works in nested calls

If run as just a script, not inside a module, it returns NULL.

{With thanks to Mike}

Comments

[Doug Osborne]

Josh,

I use the same script - I catch the NULL and return 'Query Analyzer' - so I can see if my error routine has caught someone running directly against PRODUCTION.

Oz

[Joshua Lynn]

Doug,

Good point about catching the null when your not in a module. That situation doesn't happen to me a lot since I develop mostly for SPROCs and UDFs. But anyone scripting in general or building sql code in the middle tier would have the not in module problem often.

I can see it would even be good to do when executing T-SQL scripts from the command line, or part of an Agent Job or a SSIS step.

Joshua