Monitor current transactions and get all required information

USE [master]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE Procedure [dbo].[sp_mon_exec_requests] 
as 
 
SET NOCOUNT ON;
 
select 
S.[host_name], 
        DB_NAME(R.database_id) as [database_name],
(CASE WHEN S.program_name like 'SQLAgent - TSQL JobStep (Job %' THEN  j.name ELSE S.program_name END) as Name , 
S.login_name, R.status, R.command
,b.text, R.blocking_session_id, R.percent_complete, R.session_id
    , R.wait_type,R.wait_time,
    isnull(DATEDIFF(mi, S.last_request_start_time, getdate()), 0) [MinutesRunning]
,qp.query_plan
FROM sys.dm_exec_requests R with (nolock)
INNER JOIN sys.dm_exec_sessions S with (nolock)
ON R.session_id = S.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) b
OUTER APPLY sys.dm_exec_query_plan (R.plan_handle) AS qp
LEFT OUTER JOIN msdb.dbo.sysjobs J with (nolock)
 ON (substring(left(j.job_id,8),7,2) +
substring(left(j.job_id,8),5,2) +
substring(left(j.job_id,8),3,2) +
substring(left(j.job_id,8),1,2))  = substring(S.program_name,32,8)
WHERE R.session_id > 50
and R.session_id <> @@SPID
and S.[host_name] IS NOT NULL
ORDER BY s.[host_name],S.login_name;