Technical Article

sp_lock with order by and object name

,

A kind of sp_lock with an ORDER BY possibility.
It gives also the name of the locked object (table) if executed in the same database.

declare @DataBaseID varchar (5)
set @DataBaseID = 5 
/* put here your number (get it from sp_helpdb)*/

select 	convert (smallint,req_spid) As spid,
		rsc_dbid As dbid,
		rsc_objid As ObjId,
		object_name(rsc_objid)as ObJName,
		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
		-- master.dbo.sysdatabases DB

	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 rsc_objid <> 0
			and rsc_dbid = @DataBaseID 

	order by spid

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating