Script to find all the running queries/jobs on the Database Server

Below is the query to find out all the Queries and their details like SSID etc. running on the SQL Server from all databases. It helps in finding out which queries/jobs running on the server. It can be helpful in detecting the blocking queries also.

USE Master
SET NOCOUNT ON   
SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER ON  
DECLARE @record_id int, @SQLProcessUtilization int, @CPU int,@EventTime datetime–,@MaxCPUAllowed int   
select  top 1  @record_id =record_id,
      @EventTime=dateadd(ms, -1 * ((SELECT ms_ticks from sys.dm_os_sys_info) – [timestamp]), GetDate()),– as EventTime,
      @SQLProcessUtilization=SQLProcessUtilization,
      –SystemIdle,
      –100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization,
      @CPU=SQLProcessUtilization + (100 – SystemIdle – SQLProcessUtilization) –as CPU_Usage
from (
      select
            record.value(‘(./Record/@id)[1]’, ‘int’) as record_id,
            record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) as SystemIdle,
            record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) as SQLProcessUtilization,
            timestamp
      from (
            select timestamp, convert(xml, record) as record
            from sys.dm_os_ring_buffers
            where ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
            and record like ‘%<SystemHealth>%’) as x
      ) as y
order by record_id desc 
SELECT           
            x.session_id as [Sid],
            COALESCE(x.blocking_session_id, 0) as BSid,
            @CPU as CPU,   
            @SQLProcessUtilization as SQL,  
                             
            x.Status,  
            x.TotalCPU as [T.CPU],
            x.Start_time,    
            CONVERT(nvarchar(30), getdate()-x.Start_time, 108) as Elap_time, –x.totalElapsedTime as ElapTime,
            x.totalReads as [T.RD], — total reads
            x.totalWrites as [T.WR], –total writes     
            x.Writes_in_tempdb as [W.TDB],
            (
                  SELECT substring(text,x.statement_start_offset/2,
                        (case when x.statement_end_offset = -1
                        then len(convert(nvarchar(max), text)) * 2
                        else x.statement_end_offset end – x.statement_start_offset+3)/2)
                  FROM sys.dm_exec_sql_text(x.sql_handle)
                  FOR XML PATH(”), TYPE
            ) AS Sql_text,
            db_name(x.database_id) as dbName,
            (SELECT object_name(objectid) FROM sys.dm_exec_sql_text(x.sql_handle)) as object_name,           
            x.Wait_type,
            x.Login_name,
            x.Host_name,
            CASE LEFT(x.program_name,15)
            WHEN ‘SQLAgent – TSQL’ THEN 
            (     select top 1 ‘SQL Job = ‘+j.name from msdb.dbo.sysjobs (nolock) j
                  inner join msdb.dbo.sysjobsteps (nolock) s on j.job_id=s.job_id
                  where right(cast(s.job_id as nvarchar(50)),10) = RIGHT(substring(x.program_name,30,34),10) )
            WHEN ‘SQL Server Prof’ THEN ‘SQL Server Profiler’
            ELSE x.program_name
            END as Program_name,
            x.percent_complete,
            x.percent_complete, 
            (
                  SELECT
                        p.text
                  FROM
                  (
                        SELECT
                             sql_handle,statement_start_offset,statement_end_offset
                        FROM sys.dm_exec_requests r2
                        WHERE
                             r2.session_id = x.blocking_session_id
                  ) AS r_blocking
                  CROSS APPLY
                  (
                  SELECT substring(text,r_blocking.statement_start_offset/2,
                        (case when r_blocking.statement_end_offset = -1
                        then len(convert(nvarchar(max), text)) * 2
                        else r_blocking.statement_end_offset end – r_blocking.statement_start_offset+3)/2)
                  FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
                  FOR XML PATH(”), TYPE
                  ) p (text)
            )  as blocking_text,
            (SELECT object_name(objectid) FROM sys.dm_exec_sql_text(
            (select top 1 sql_handle FROM sys.dm_exec_requests r3 WHERE r3.session_id = x.blocking_session_id))) as blocking_obj
       
      FROM
      (
            SELECT
                  r.session_id,
                  s.host_name,
                  s.login_name,
                  r.start_time,
                  r.sql_handle,
                  r.database_id,
                  r.blocking_session_id,
                  r.wait_type,
                  r.status,
                  r.statement_start_offset,
                  r.statement_end_offset,
                  s.program_name,
                  r.percent_complete,               
                  SUM(cast(r.total_elapsed_time as bigint)) /1000 as totalElapsedTime, –CAST AS BIGINT to fix invalid data convertion when high activity
                  SUM(cast(r.reads as bigint)) AS totalReads,
                  SUM(cast(r.writes as bigint)) AS totalWrites,
                  SUM(cast(r.cpu_time as bigint)) AS totalCPU,
                  SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
            FROM sys.dm_exec_requests r
            JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
            JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
            WHERE r.status IN (‘running’, ‘runnable’, ‘suspended’)
            GROUP BY
                  r.session_id,
                  s.host_name,
                  s.login_name,
                  r.start_time,
                  r.sql_handle,
                  r.database_id,
                  r.blocking_session_id,
                  r.wait_type,
                  r.status,
                  r.statement_start_offset,
                  r.statement_end_offset,
                  s.program_name,
                  r.percent_complete
      ) x
      where x.session_id <> @@spid
      order by x.totalCPU desc
GO

I have get these helpful standard scripts through other sites like MSDN, technet etc. Just sharing so that other can also get benefited by using it.