use master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_blockinglocks'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_blockinglocks
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_blockinglocks'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_blockinglocks >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_blockinglocks >>>'
END
go
create procedure sp_blockinglocks --- 1999/01/18 00:00

as
	select 	DISTINCT convert (smallint, l1.req_spid) As spid, 
		left(db_name(l1.rsc_dbid), 10) As dbName, 
		left(object_name(l1.rsc_objid), 20) AS ObjName,
		l1.rsc_indid As IndId,
		substring (v.name, 1, 4) As Type,
		substring (l1.rsc_text, 1, 16) as Resource,
		substring (u.name, 1, 8) As Mode,
		substring (x.name, 1, 5) As Status

	from 	master.dbo.syslockinfo l1,
		master.dbo.syslockinfo l2,
		master.dbo.spt_values v,
		master.dbo.spt_values x,
		master.dbo.spt_values u

	where          l1.rsc_type = v.number
			and v.type = 'LR'
			and l1.req_status = x.number
			and x.type = 'LS'
			and l1.req_mode + 1 = u.number
			and u.type = 'L'
			and l1.rsc_type <>2 /* not a DB lock */
			and l1.rsc_dbid = l2.rsc_dbid
			and l1.rsc_bin = l2.rsc_bin
                        and l1.rsc_objid = l2.rsc_objid 
			and l1.rsc_indid = l2.rsc_indid 
			and l1.req_spid <> l2.req_spid
			and l1.req_status <> l2.req_status
	order by substring (l1.rsc_text, 1, 16), substring (x.name, 1, 5) 


return (0)
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_blockinglocks'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_blockinglocks To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_blockinglocks >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_blockinglocks >>>'
go