select id = p.blocked, id_blk = p.spid, "time" = p.time_blocked,
"table" = convert(varchar(30), db_name(l.dbid) + '..' + object_name(l.id,
l.dbid)),
"type" = v.name, l.class,
"object" = object_name(p2.id, l.dbid),
"login blk" = suser_name(p.suid),
"login" = suser_name(p2.suid),
"program" = p2.program_name,
"tran blk" = p.tran_name,
"tran" = p2.tran_name
from master..sysprocesses p,
master..syslocks l,
master..spt_values v,
master..sysprocesses p2
where p.blocked > 0
and p.blocked = l.spid
and p.blocked = p2.spid
and l.spid= p2.spid
and l.type = v.number
and v.type = "L"
and v.name like '%blk%'
--and p.time_blocked > 1 -- on seconds
go