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

This page was last updated on May 01, 2006 04:28 PM.