LearnItFirst User Forum

USE master
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_lock3'), 'IsProcedure')) = 1
    DROP PROC dbo.sp_lock3
GO
CREATE PROC dbo.sp_lock3 (
    @spid1 INT = NULL /* Check only this spid; if this is NULL then all spids will be checked */
    , @spid2 INT = NULL /* and this spid; if this is not null, @spid1 must be not null as well */
)
AS
/*
Author: Scott Whigham FROM http://www.LearnSqlServer.com/

Description: This is an improved version of sp_lock2, the "enhanced" version of sp_lock FROM kb q255596.
        I don't know who wrote the original sp_lock2 but they never tested it against Windows authentication names, that's for sure!
        The returned login name was only 20 characters... Anyway, I increased a few datatypes AND formatted the code more to my "style"
        among other changes.

        Changes:
            (1) Styling and removing unnecessary syntax
            (2) Changed join syntaxes to easier-to-read syntax
            (3) Better documented
            (4) Removed unnecessary cursor in favor of reusing #lockinfo temp table
            (5) Ensured no interleaving of ddl code in proc by moving CREATE TABLE to beginning
            (6) Changed EXEC (@DynamicSql) to sp_executesql
            (7) Significantly improved performance. On my test server, I was seeing decent improvements in    
                    execution speed
            (8) Changed datatype usage to allow for Windows Authentication logins, long machine names and
                    long object names

Misc Notes: The original sp_lock2 that this was built from can be downloaded here: http://support.microsoft.com/kb/q255596/

Versions: SQL Server 2000, 7.0
                    -- SQL Server 2005 users: you will want to use sp_lock. It has been improved and is better than this or sp_lock2

Creation Date: August 30, 2006

For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
CREATE TABLE #locktable (
    spid SMALLINT
    , loginname NVARCHAR(128)
    , hostname NVARCHAR(128)
    , dbid INT
    , dbname NVARCHAR(128)
    , objId INT
    , ObjName  NVARCHAR(128)
    , IndId INT
    , Type NVARCHAR(4)
    , Resource NVARCHAR(16)
    , Mode NVARCHAR(8)
    , Status NVARCHAR(5)
)

SET NOCOUNT ON

IF @spid2 IS NOT NULL AND @spid1 IS NULL
    SET @spid1 = @spid2

DECLARE @object_id INT,
  @dbid INT,
  @DynamicSql NVARCHAR(255)

/***** @spid1 is  provided so show only the locks for @spid1 and @spid2 *****/
IF @spid1 IS NOT NULL
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid)
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (v.name,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (u.name,  1,  8)
        , SUBSTRING (x.name,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS'      AND  u.type = 'L' AND l.req_spid in (@spid1, @spid2)

ELSE /***** @spid1 is not provided so show all the locks *****/
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid)
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (v.name,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (u.name,  1,  8)
        , SUBSTRING (x.name,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L'

/**********************************************************************************************
    Because the locks exist in any database, you must USE <database name> before running OBJECT_NAME

    We use a dynamic SQL loop to loop through each row from #locktable

    A temp table is required here since SQL Server 2000 cannot access a table variable when issuing dynamic sql
**********************************************************************************************/
-- Initialize the loop
SELECT TOP 1 @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''

WHILE @dbid IS NOT NULL
    BEGIN
        SELECT @DynamicSql =
         'USE ' + DB_NAME(@dbid) + char(13)
         + 'UPDATE #locktable SET ObjName = OBJECT_NAME('
            + CONVERT(VARCHAR, @object_id) + ') WHERE dbid = ' + CONVERT(VARCHAR, @dbId)
         + ' AND objid = ' + CONVERT(VARCHAR, @object_id)

        EXEC sp_executesql @DynamicSql

        SET @dbid = NULL -- TSQL preserves the "old" value unless you initialize it to NULL
        SELECT @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''
    END

SELECT * FROM #locktable
    WHERE objname NOT LIKE '#locktable_____%' -- don't return this temp table
        AND objid > 100 -- do not return system table locks
        AND objname <> 'spt_values'
GO