With T
As
(
Select ROW_NUMBER() over (order by spid) as id, spid,blocked From (select distinct spid,blocked from sys.sysprocesses where spid in
(Select blocked from sys.sysprocesses where blocked <>0) and blocked=0) S
Union All
Select id,TB.spid,TB.blocked From sys.sysprocesses TB Inner Join T on TB.blocked=T.SPID AND TB.blocked<>TB.spid
),
U
AS
(select distinct a.spid,last_batch,program_name,nt_username,loginame,db_name(a.dbid) as 'dbname',OBJECT_NAME(resource_associated_entity_id,a.dbid) as 'tablename',request_mode,request_type,open_tran,waittype,status,hostname,cmd,b.text as TSQL
from sys.sysprocesses a
cross apply sys.dm_exec_sql_text(a.sql_handle) b
inner join sys.dm_tran_locks c on a.spid=c.request_session_id and resource_type='OBJECT' and request_mode not like 'Sch%'
where spid in
(Select blocked from sys.sysprocesses where blocked <>0)
and a.blocked=0
)
select U.spid,blockeds,last_batch,program_name,nt_username,loginame,dbname,tablename,request_mode,request_type,open_tran,waittype,status,hostname,cmd,tsql from U inner join (Select distinct COUNT(id) over (partition by id)-1 as blockeds,C.spid From T A cross apply (select TOP 1 SPID from T B WHERE A.id=B.id AND B.blocked=0)C) BlKS ON U.spid=BLKS.spid;
