Report Locking on Specific Database

USE Master
GO

IF EXISTS(SELECT name 
          FROM sysobjects 
          WHERE name = 'sp_ReportLocking' 
            AND type = 'P')
  DROP PROC sp_ReportLocking
GO

CREATE PROC sp_ReportLocking  
    @DBName sysname = 'master',  
    @LockType varchar(20) = 'Application',  
    @WriteTable nvarchar(100) = NULL  
/*    
    Name:       sp_ReportLocking  
    Type:       User Stored Procedure (for master 
                database)  
    Input:      @DBName     sysname : name of database to 
                      report locking on  
                @LockType   varchar(20)   : minimum level 
                      of locking to report on Applicable 
                      Levels: Database, File, Index, Table, 
                      Page, Key, Extent, RID, Application  
                @WriteTable nvarchar(100) : Table to output 
                      results to.  If null, displays to 
                      client  
    Output:     If @WriteTable is NULL, displays locks to
                client as result set  
    Purpose:    Reports locking on a given database  
    Written By: K. Brian Kelley, bk@warpdrivedesign.org  
    Version:    1.0  
    Date:       Created: December 11, 2001  
*/    
  
AS  
  
SET NOCOUNT ON  
  
IF NOT EXISTS (SELECT name 
               FROM master.dbo.sysdatabases WITH (nolock) 
               WHERE name = @DBName)  
BEGIN  
 RAISERROR('Invalid Database. Database (%s) not found.' ,16, 1, @DBName)  
 RETURN -1  
END  
  
DECLARE @SQL     nvarchar(4000),  
        @LockTypeID varchar(2)  
  
SET @LockTypeID = (SELECT CASE @LockType  
 WHEN 'Database' THEN '2'  
 WHEN 'File' THEN '3'  
 WHEN 'Index' THEN '4'  
 WHEN 'Table' THEN '5'  
 WHEN 'Page' THEN '6'  
 WHEN 'Key' THEN '7'  
 WHEN 'Extent' THEN '8'  
 WHEN 'RID' THEN '9'  
 WHEN 'Application' THEN '10'  
 ELSE '10'  
 END)  
  
  
IF @WriteTable IS NOT NULL  
BEGIN  
 IF OBJECT_ID(@WriteTable) IS NULL  
 BEGIN  
  RAISERROR('Error: Invalid Table. Table (%s) not found.', 16, 1, @WriteTable)  
  RETURN -1  
 END  
  
 SET @SQL = 'INSERT ' + @WriteTable + ' ([Object], [TableIndex], [LockType], 

[LockMode], [LockStatus], [ObjectDescription], [LockTime], [SPID])   
'  
END  
ELSE  
 SET @SQL = ''  
  
SET @SQL = @SQL + 'SELECT so.name [Object], si.name [Index], CASE rsc_type   
 WHEN 2 THEN ''Database''  
 WHEN 3 THEN ''File''  
 WHEN 4 THEN ''Index''  
 WHEN 5 THEN ''Table''  
 WHEN 6 THEN ''Page''  
 WHEN 7 THEN ''Key''  
 WHEN 8 THEN ''Extent''  
 WHEN 9 THEN ''RID''  
 WHEN 10 THEN ''Application''  
 ELSE ''Other''  
END [LockType], CASE req_mode  
 WHEN 1 THEN ''Sch-S''  
 WHEN 2 THEN ''Sch-M''  
 WHEN 3 THEN ''S''  
 WHEN 4 THEN ''U''  
 WHEN 5 THEN ''X''  
 WHEN 6 THEN ''IS''  
 WHEN 7 THEN ''IU''  
 WHEN 8 THEN ''IX''  
 WHEN 9 THEN ''SIU''  
 WHEN 10 THEN ''SIX''  
 WHEN 11 THEN ''UIX''  
 WHEN 12 THEN ''BU''  
 WHEN 13 THEN ''RangeS_S''  
 WHEN 14 THEN ''RangeS_U''  
 WHEN 15 THEN ''RangeI_N''  
 WHEN 16 THEN ''RangeI_S''  
 WHEN 17 THEN ''RangeI_U''  
 WHEN 18 THEN ''RangeI_X''  
 WHEN 19 THEN ''RangeX_S''  
 WHEN 20 THEN ''RangeX_U''  
 WHEN 21 THEN ''RangeX_X''  
 ELSE ''Other''  
END [LockMode], CASE req_status  
 WHEN 1 THEN ''Granted''  
 WHEN 2 THEN ''Converting''  
 WHEN 3 THEN ''Waiting''  
END [LockStatus], rsc_text [ObjectDescription], GETDATE() [LockTime], req_SPID 

[SPID]  
FROM master.dbo.syslockinfo sl WITH (nolock) JOIN [' + @DBName + '].dbo.sysobjects 

so WITH (nolock) ON sl.rsc_objid = so.id  
 LEFT JOIN [' + @DBName + '].dbo.sysindexes si WITH (nolock) ON sl.rsc_objid = si.id 

AND sl.rsc_indid = si.indid  
WHERE rsc_dbid = ' + CAST(DB_ID(@DBName) AS varchar) + '  
 AND rsc_type <= ' + @LockTypeID + '  
ORDER BY [Object], rsc_type, req_mode'  
  
EXEC(@SQL)  

GO

CREATE TABLE [CaptureLocking] (
[Object] [sysname] NOT NULL,
[TableIndex] [sysname] NULL,
[LockType] [varchar] (30),
[LockMode] [varchar] (10),
[LockStatus] [varchar] (10),
[ObjectDescription] [nchar] (32),
[LockTime] [datetime] NOT NULL ,
[SPID] [int] NOT NULL
)
GO