title: Retrieving the Name of Running Stored Procedures Across Multiple Databases
body: <p>I'm trying to write a query that reports the current database activity.  The query links together various DMV's like <code>sys.dm_exec_connections</code>, <code>sys.dm_exec_sessions</code>, <code>sys.dm_exec_requests</code>, etc.  The query also pulls the actual queries being run via the <code>sys.dm_exec_sql_text</code> function.</p>
  
  <p>(I'm aware of Activity Monitor and SQL Profiler.  I need to gather this information up in  a query, so neither of these programs are relevant here.)</p>
  
  <p>Much of the activity in our systems takes place in stored procedures and functions.  It would be nice to see the names of these procedures in this query.</p>
  
  <p>My question is:</p>
  
  <p><strong>How do I reliably display the name of the stored procedures or functions being executed?</strong></p>
  
  <p>I'm aware that the <code>sys.dm_exec_sql_text</code> function returns an <code>objectid</code>, and that I can join this <code>objectid</code> to <code>sys.objects</code>.  The problem is, <em>there are multiple databases on this server, and sys.objects only applies to the current database</em>.  I want this query to be able to show the running object name no matter what database the query happened to be run against.</p>
  
  <p>So far the only solution I have is to use <code>sp_msforeachdb</code> create a temp table containing all the object IDs and names from all databases and join to this table from the result of the <code>dm_exec_sql_text</code> function.</p>
  
  <p>Is there a better solution to the temp table approach?  I feel like I'm missing something.</p>