Show Blocking and Wait time

/* 2001/12/17-JKJ */
set nocount on
go
select 
'Blocking:' As LockStatus, 
cast (mo.req_spid as smallint) As spid,
mo.rsc_dbid As dbid,
mo.rsc_objid As ObjId,
mo.rsc_indid As IndId,
substring (vo.name, 1, 4) As Type,
substring (mo.rsc_text, 1, 16) as Resource,
substring (uo.name, 1, 8) As Mode,
substring (xo.name, 1, 5) As Status,
0 As WaitTime,
substring( object_name(mo.rsc_objid), 1, 24) As Object

from 
master.dbo.syslockinfo mo,
master.dbo.spt_values vo,
master.dbo.spt_values xo,
master.dbo.spt_values uo

where   
mo.rsc_type = vo.number
and vo.type = 'LR'
and mo.req_status = xo.number
and xo.type = 'LS'
and mo.req_mode + 1 = uo.number
and uo.type = 'L'
and substring (xo.name, 1, 5) = 'Grant'
and exists 

(select 1 from 
    master.dbo.syslockinfo mi,
 master.dbo.spt_values vi,
 master.dbo.spt_values xi,
 master.dbo.spt_values ui
 where 
mi.rsc_type = vi.number
and vi.type = 'LR'
and mi.req_status = xi.number
and xi.type = 'LS'
and mi.req_mode + 1 = ui.number
and ui.type = 'L'
and mi.rsc_dbid = mo.rsc_dbid 
and mi.rsc_objid = mo.rsc_objid 
and mi.rsc_indid = mo.rsc_indid 
and substring (vi.name, 1, 4) = substring (vo.name, 1, 4) 
and substring (mi.rsc_text, 1, 16) = substring (mo.rsc_text, 1, 16) 
and substring (xi.name, 1, 5) = 'Wait'
)

union all

select 
'Waiting:' As LockStatus,
cast (m.req_spid as smallint) As spid,
m.rsc_dbid As dbid,
m.rsc_objid As ObjId,
m.rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status,
sp.waittime as WaitTime,
substring( object_name(m.rsc_objid), 1, 24) As Object

from 
master.dbo.syslockinfo m,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysprocesses sp

where   
m.rsc_type = v.number
and v.type = 'LR'
and m.req_status = x.number
and x.type = 'LS'
and m.req_mode + 1 = u.number
and u.type = 'L'
and substring (x.name, 1, 5) = 'Wait'
and cast( sp.spid as smallint ) = cast( m.req_spid as smallint )
  and sp.waitresource = 
case substring( v.name, 1, 3)
when 'RID' then 
substring ( v.name, 1, 3) + ': ' + cast( m.rsc_dbid as varchar(2) ) + ':' + substring (m.rsc_text, 1, 16)
when 'KEY' then
substring ( v.name, 1, 3) + ': ' + cast( m.rsc_dbid as varchar(2) ) + ':' + cast( m.rsc_objid as varchar(30) ) + ':' + cast( m.rsc_indid as varchar(2) ) + ' ' + substring (m.rsc_text, 1, 16)
when 'PAG' then 
substring ( v.name, 1, 3) + ': ' + cast( m.rsc_dbid as varchar(2) ) + ':' + substring (m.rsc_text, 1, 16)
when 'TAB' then 
substring ( v.name, 1, 3) + ': ' + cast( m.rsc_dbid as varchar(2) ) + ':' + cast( m.rsc_objid as varchar(30) ) + ' []'
end

order by

Object, 
WaitTime 

go
set nocount off
go