改进sp_lock

  在SQLserver2000，想看那个表被锁并且显示被锁的表名，并没有直接可以用的存储过程可用。我根据系统存储sp_lock重写了一个sp_lock_jj的过程，可以直接看到。代码如下：

 

create procedure sp_lock_jj --- 2005/04/08 00:00 
@spid1 int = NULL,  /* server process id to check for locks */ 
@spid2 int = NULL  /* other process id to check for locks */ 
as 
 
set nocount on 
/* 
**  Show the locks for both parameters. 
*/ 
if @spid1 is not NULL 
begin 
 select  convert (smallint, req_spid) As spid, 
  rsc_dbid As dbid, 
  rsc_objid As ObjId, 
 Object_name(rsc_objid) as objectname,
  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 
 
 from  master.dbo.syslockinfo, 
  master.dbo.spt_values v, 
  master.dbo.spt_values x, 
  master.dbo.spt_values u 
 
 where   master.dbo.syslockinfo.rsc_type = v.number 
   and v.type = 'LR' 
   and master.dbo.syslockinfo.req_status = x.number 
   and x.type = 'LS' 
   and master.dbo.syslockinfo.req_mode + 1 = u.number 
   and u.type = 'L' 
 
   and req_spid in (@spid1, @spid2) 
end 
 
/* 
**  No parameters, so show all the locks. 
*/ 
else 
begin 
 select  convert (smallint, req_spid) As spid, 
  rsc_dbid As dbid, 
  rsc_objid As ObjId, 
 Object_name(rsc_objid) as objectname,   ----显示表名
  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 
 
 from  master.dbo.syslockinfo, 
  master.dbo.spt_values v, 
  master.dbo.spt_values x, 
  master.dbo.spt_values u 
 
 where   master.dbo.syslockinfo.rsc_type = v.number 
   and v.type = 'LR' 
   and master.dbo.syslockinfo.req_status = x.number 
   and x.type = 'LS' 
   and master.dbo.syslockinfo.req_mode + 1 = u.number 
   and u.type = 'L' 
 order by spid 
end 
 
return (0) -- sp_lock _jj