Show_Locks

CREATE   PROC HPSP_UT_ShowLocks

AS
SET ANSI_NULLS OFF 
SET NOCOUNT ON

create table #Locks(
     spid int       
,    dbid int
,   ObjIdint     
,   IndIdint
,   Typevarchar(4)
,   Resourcevarchar(16)
,   Modevarchar(8)
,   Status            varchar(5)           
)                 

INSERT          #Locks
EXEC                sp_lock


SELECT DISTINCT p.spid,  p.status, ObjId, o.name TblName,  --p.ecid,
   RTRIM(nt_username) username,
   RTRIM(loginame)  loginame,
   hostname ,
CONVERT(char(5),blocked)  blk, 
  CASE
when p.dbid = 0 then null
when p.dbid <> 0 then db_name(p.dbid)
end   dbname 
   ,cmd

 FROM master.dbo.sysprocesses p
  JOIN (SELECT DISTINCT spid,  dbid, ObjId, Mode FROM       #Locks      
WHERE      Mode in('X', 'IX', 'SIX')       
)  l
on p.spid = l.spid
and p.dbid = l.dbid
JOIN dbo.sysobjects o
on o.id = ObjId


drop table #Locks