sp_lock2 와 sp_lockcount

http://weblogs.asp.net/mnolton/archive/2009/05/15/sp-lock2-and-sp-lockcount.aspx

Here are two helpful procedures to help with sqlserver database work. 

1. The code for both of these are based on the system stored procedures sys.sp_lock that comes built in the master database.
2. build both of these in your master database if you want to use them in all databases across your database instance.

• sp_lock2 -- provides object_name for the object_id normally presented with sp_lock
• sp_lockcount -- provides a count of the locks. useful when doing etl processes and you are monitoring locks but do not care to receive the tens of thousands of lock data coming back...when all you want to know is the count.

sp_lock2 code

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================================
-- Author:        Mathew Nolton (based on sp_lock in sqlserver)
-- create date: 05/15/2009
-- Description:    Returns lock information similar to sp_lock but
--                with additional object name information.
-- ==================================================================
create procedure [dbo].[sp_lock2]
    @spid1 int = NULL,        /* server process id to check for locks */
    @spid2 int = NULL        /* other process id to check for locks */
as
    -- ======================================================================================================
    -- set options
    -- ======================================================================================================
    set nocount on
    set transaction isolation level read committed

    -- ======================================================================================================
    -- do the work.
    -- ======================================================================================================
    select     convert (smallint, [req_spid])    as [spid],
            [rsc_dbid]                        as [dbid],
            db_name([rsc_dbid])                as [dbname],
            [rsc_objid]                        as [ObjId],
            object_name([rsc_objid])        as [ObjName],
            [rsc_indid]                        as [IndId],
            object_name([rsc_indid])        as [IndName],
            substring ([v].[name], 1, 4)    as [Type],
            substring ([rsc_text], 1, 32)    as [Resource],
            substring ([u].[name], 1, 8)    as [Mode],
            substring ([x].[name], 1, 5)    as [Status]
    from     [master].[dbo].[syslockinfo] [s]
            inner join [master].[dbo].[spt_values] [v]
    on        [s].[rsc_type]        = [v].[number]
            inner join [master].[dbo].[spt_values] [x]
    on        [s].[req_status]    = [x].[number]
            inner join [master].[dbo].[spt_values] [u]
    on        [s].[req_mode] + 1    = [u].[number]
    where    [v].[type]            = 'LR'
    and        [x].[type]            = 'LS'
    and        [u].[type]            = 'L'
    and        ( @spid1 is null or [req_spid] in (@spid1, @spid2) )
    order by [spid]

sp_lockcount code

-- ==================================================================
-- Author:        Mathew Nolton (based on sp_lock in sqlserver)
-- create date: 05/15/2009
-- Description:    Returns count of locks.
-- ==================================================================
create procedure [dbo].[sp_lockcount]
    @spid1 int = NULL,        /* server process id to check for locks */
    @spid2 int = NULL        /* other process id to check for locks */
as
    -- ======================================================================================================
    -- set options
    -- ======================================================================================================
    set nocount on
    set transaction isolation level read committed

    -- ======================================================================================================
    -- do the work.
    -- ======================================================================================================
    select     count(*)
    from     [master].[dbo].[syslockinfo] [s]
            inner join [master].[dbo].[spt_values] [v]
    on        [s].[rsc_type]        = [v].[number]
            inner join [master].[dbo].[spt_values] [x]
    on        [s].[req_status]    = [x].[number]
            inner join [master].[dbo].[spt_values] [u]
    on        [s].[req_mode] + 1    = [u].[number]
    where    [v].[type]            = 'LR'
    and        [x].[type]            = 'LS'
    and        [u].[type]            = 'L'
    and        ( @spid1 is null or [req_spid] in (@spid1, @spid2) )