SQLTeam.com | Weblogs | Forums

What is the procedure name



In sp_who2 I can find a blocked SPID. By using DBCC INPUTBUFFER I can find the procedure name (the EventInfo column).

Is the procedure name available from a system table?

I know how to get the procedure text from sys.dm_exec_sql_text, but I do not know where to find the procedure name. (I found program_name but that is not what I want).

Thank you,

 Select object_name(est.objectid) As ObjectName
      , est.[text] As sql_text
   From sys.dm_exec_sessions                   es
  Inner Join sys.dm_exec_connections           ec On ec.session_id = es.session_id
  Outer Apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) est


Be sure to include the database id in the OBJECT_NAME function, in case the object is not in the current db:

SELECT OBJECT_NAME(est.objectid, ISNULL(est.dbid, DB_ID()))


Thank you, @jeffw8713 and @ScottPletcher. I read the dm_exec_sql_ text and missed that it had objectID. Adding the databaseID is very helpful as what I am doing is creating a home grown sp_who.