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}