SQL Server - Show Current Locks

gistfile1.txt

SELECT 
    HostName,
    "OS UserName",
    Login, 
    spid, 
    "Database", 
    TableID,
    "Table Name", 
    IndID,   
    "Lock Type", 
    "Lock Mode", 
	LM,
    Status, 
  Resource,
    COUNT(*) AS "Lock Count"
FROM (
    SELECT
        CONVERT(VARCHAR(30), RTRIM(P.HostName)) AS 'HostName',
        CONVERT(VARCHAR(30), RTRIM(P.nt_UserName)) AS 'OS UserName',
        CONVERT(VARCHAR(30), SUSER_SNAME(p.sid)) AS 'Login', 
        CONVERT(SMALLINT,req_spid) AS 'spid',
        CONVERT(VARCHAR(30), DB_NAME(rsc_dbid)) AS 'Database',
        rsc_objid AS 'TableID',
		CONVERT(VARCHAR(30), OBJECT_NAME(rsc_objid, rsc_dbid)) AS 'Table Name',
        rsc_indid AS 'IndID', 
        CASE SUBSTRING (lock_type.name, 1, 4) 
            WHEN '' THEN 'None'
            WHEN 'DB' THEN 'Database'
            WHEN 'FIL' THEN 'File'
            WHEN 'IDX' THEN 'Index'
            WHEN 'TAB' THEN 'Table'
            WHEN 'PAG' THEN 'Page'
            WHEN 'KEY' THEN 'Key'
            WHEN 'EXT' THEN 'Extent'
            WHEN 'RID' THEN 'Row ID'
            WHEN 'APP' Then 'Application'
            Else SubString (lock_type.name, 1, 4)
        END AS 'Lock Type',		
        Case SubString (lock_mode.name, 1, 12)
            When NULL Then 'N/A'
            When 'Sch-S' Then 'SCHEMA (Stability)'--'SCHEMA stability lock'
            When 'Sch-M' Then 'SCHEMA (Modification)'--'SCHEMA modification lock'
            When 'S' Then 'SHARED'--'SHARED Lock acquisition'
            When 'U' Then 'UPDATE'--'UPDATE lock acquisition'
            When 'X' Then 'EXCLUSIVE'--'EXCLUSIVE lock granted'
            When 'IS' Then 'SHARED (Intent)'--'INTENT for SHARED lock'
            When 'IU' Then 'UPDATE (Intent)'--'INTENT for UPDATE lock'
            When 'IX' Then 'EXCLUSIVE (Intent)'--'INTENT for EXCLUSIVE lock'
            When 'SIU' Then 'SHARED (Intent UPDATE)'--'SHARED lock with INTENT for UPDATE'
            When 'SIX' Then 'SHARED (Intent EXCLUSIVE)'--'SHARED lock with INTENT for EXCLUSIVE'
            When 'UIX' Then 'UPDATE'--'UPDATE lock with INTENT for EXCLUSIVE'
            When 'BU' Then 'UPDATE (BULK)'--'BULK UPDATE lock'
            Else SubString (lock_mode.name, 1, 12)
        END AS 'Lock Mode', 
		SubString (lock_mode.name, 1, 12) AS 'LM',
        SubString(lock_status.name, 1, 5) AS 'Status',
        SubString (rsc_text, 1, 16) AS 'Resource'
    FROM 
        Master..SysLockInfo S
        JOIN Master..spt_values lock_type on S.rsc_type = lock_type.number
        JOIN Master..spt_values lock_status on S.req_status = lock_status.number
        JOIN Master..spt_values lock_mode on S.req_mode = lock_mode.number -1
        JOIN Master..SysProcesses P on S.req_spid = P.spid
    WHERE
            lock_type.type = 'LR'
        AND lock_status.type = 'LS'
        AND lock_mode.type = 'L'
        AND DB_Name(rsc_dbid) NOT IN ('master', 'msdb', 'model')
    ) AS X
WHERE TableID > 0
GROUP BY 
    [HostName],
    [OS UserName],
    [Login], 
    [spid], 
    [Database], 
    [TableID],
    [Table Name], 
    [IndID], 
    [Lock Type], 
    [Lock Mode], 
	[LM],
    [Status],
	resource
ORDER BY
    [spid], [Database], [Table Name], [Lock Type], [Login]

--- Check the Messages Tab
DBCC opentran