In SQLServer2000, you want to see that table is locked and displayed the latched table name, and there is no direct stored procedure available. I renooked a sp_lock_jj process according to the system storage sp_lock, which can be seen directly. code show as below:
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 .slockinfo.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, ---- display table 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'
