I'm trying to write a query that reports the current database activity. The query links together various DMV's like `sys.dm_exec_connections`, `sys.dm_exec_sessions`, `sys.dm_exec_requests`, etc. The query also pulls the actual queries being run via the `sys.dm_exec_sql_text` function.

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

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.

My question is:

**How do I reliably display the name of the stored procedures or functions being executed?**

I'm aware that the `sys.dm_exec_sql_text` function returns an `objectid`, and that I can join this `objectid` to `sys.objects`. The problem is, *there are multiple databases on this server, and sys.objects only applies to the current database*. I want this query to be able to show the running object name no matter what database the query happened to be run against.

So far the only solution I have is to use `sp_msforeachdb` create a temp table containing all the object IDs and names from all databases and join to this table from the result of the `dm_exec_sql_text` function.

Is there a better solution to the temp table approach? I feel like I'm missing something.