Return Query Text Along With sp_who2 Using Dynamic Management Views in SQL Server 2005

Sp_Who2 is used to check which SPID blocked by other SPID with some other information like DBName,Staus,Login,Command,ProgramName etc but this Sp is not give us the exact query that is run against SPID.

Below is the stored procedure that we use to get all SP_who2 results along with query text.


Create procedure who_blk_with_command
as
SELECT D.text SQLStatement, A.Session_ID SPID,
ISNULL(B.status,A.status) Status, A.login_name [Login],
A.[host_name] HostName, C.BlkBy, DB_NAME(B.Database_ID) DBName,
B.command, ISNULL(B.cpu_time, A.cpu_time) CPUTime,
ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,
A.last_request_start_time LastBatch, A.program_name FROM
sys.dm_exec_sessions A
LEFT JOIN sys.dm_exec_requests B ON A.session_id = B.session_id
LEFT JOIN
( SELECT A.request_session_id SPID,
B.blocking_session_id BlkBy
FROM sys.dm_tran_locks as A
INNER JOIN sys.dm_os_waiting_tasks as B
ON A.lock_owner_address = B.resource_address ) C
ON A.Session_ID = C.SPID
OUTER APPLY sys.dm_exec_sql_text(sql_handle) D