SQLTeam.com | Weblogs | Forums

What is the procedure name

sql2008r2

#1

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,
DJJ


#2
 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

#3

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


#4

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.