Retrieving the Name of Running Stored Procedures Across Multiple Databases

Question

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.

Answer 1

I would recommend Adam Machanic's excellent sp_WhoISActive. It doesn't return the exact object name, but does return the sql command being executed in a nice clickable form.

Answer 2

--I use the following proc:

USE [master]
GO

CREATE PROC [dbo].[sp_who3] 
AS

    SET NOCOUNT ON 

    DECLARE @LoginName varchar(128)
    DECLARE @AppName varchar(128)

    SELECT [SPID] = s.[spid]
        , [CPU] = s.[cpu]
        , [Physical_IO] = s.[physical_io]
        , [Blocked] = s.[blocked]
        , [LoginName] = CONVERT([sysname], RTRIM(s.[Loginame]))
        , [Database] = d.[name]
        , [AppName] = s.[program_name]
        , [HostName] = s.[hostname]
        , [Status] = s.[Status]
        , [Cmd] = s.[cmd]
        , [Last Batch] = s.[last_batch]
        , [Kill Command] = 'Kill ' + CAST(s.[spid] AS varchar(10))
        , [Buffer Command] = 'DBCC InputBuffer(' + CAST(s.[spid] AS varchar(10)) 
                                                         + ')'
    FROM [master].[dbo].[sysprocesses] s WITH(NOLOCK)
    JOIN [master].[sys].[databases] d WITH(NOLOCK)
                ON s.[dbid] = d.[database_id]
    WHERE s.[Status]  'background'
        AND s.[spid]  @@SPID  --@CurrentSpid@
    ORDER BY s.[blocked] DESC, s.[physical_io] DESC, s.[cpu] DESC, CONVERT([sysname], RTRIM(s.[Loginame]))

    BEGIN
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

        SELECT [Spid] = er.[session_Id]
        , [ECID] = sp.[ECID]
        , [Database] = DB_NAME(sp.[dbid])
        , [User] = [nt_username]
        , [Status] = er.[status]
        , [Wait] = [wait_type]
        , [Individual Query] = SUBSTRING(qt.[text], er.[statement_start_offset] / 2, (CASE WHEN er.[statement_end_offset] = - 1 THEN LEN(CONVERT(VARCHAR(MAX), qt.[text])) * 2
                            ELSE er.[statement_end_offset] END - er.[statement_start_offset]) / 2)
        , [Parent Query] = qt.[text]
        , [Program] = sp.[program_name]
        , [Hostname] = sp.[Hostname]
        , [Domain] = sp.[nt_domain]
        , [Start_time] = er.[Start_time]
        FROM [sys].[dm_exec_requests] er WITH(NOLOCK)
        INNER JOIN [sys].[sysprocesses] sp WITH(NOLOCK)
                ON er.[session_id] = sp.[spid]
        CROSS APPLY [sys].[dm_exec_sql_text](er.[sql_handle]) qt 
        WHERE er.[session_Id] > 50                      -- Ignore system spids.
            AND er.[session_Id] NOT IN (@@SPID)     -- Ignore the current statement.
        ORDER BY er.[session_Id], sp.[ECID]
    END
GO