USE master
GO
CREATE procedure sp_lock2
@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.
** Do not show locks in master, tempdb, model or msdb
*/
if @spid1 is not NULL
begin
        select         convert (smallint, req_spid) As spid,
                convert(char(20),db_name(rsc_dbid)) As [Database],
                rsc_objid As ObjId,
                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 rsc_dbid >=5
                        and req_spid in (@spid1, @spid2)
end

/*
** No parameters, so show all the locks.
*/
else
begin
        select         convert (smallint, req_spid) As spid,
                convert(char(20),db_name(rsc_dbid)) As [Database],
                rsc_objid As ObjId,
                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 rsc_dbid >=5
        order by spid
end

return (0) -- sp_lock2