Script of the day - Locking Information

This one is an oldie but a goodie. I don't remember who originally gave me this, but I've edited it over the years. Works on SQL Server 2000 and 2005.

SELECT LTRIM( rtrim( hostname ) ) HostName,

spid ProcesId,

Program_Name,

rsc_dbid DBID,

DB_NAME( rsc_dbid ) DBNAME,

CASE rsc_type

WHEN 1 THEN 'No resource used'

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 (Row ID)'

WHEN 10 THEN 'Application'

END ResourceType,

rsc_objid OBJID,

OBJECT_NAME( rsc_objid ) OBJNAME,

CASE req_mode

WHEN 0 THEN 'No access to recource'

WHEN 1 THEN 'Sch-S: Schema Stability'

WHEN 2 THEN 'Sch-M: Schema Modification'

WHEN 3 THEN 'S: Shared'

WHEN 4 THEN 'U: Update'

WHEN 5 THEN 'X: Exclusive'

WHEN 6 THEN 'IS: Intent Shared'

WHEN 7 THEN 'IU: Intent Update'

WHEN 8 THEN 'IX: Intent Exclusive'

WHEN 9 THEN 'SIU: Shared Intent Update'

WHEN 10 THEN 'SIX: Shared Intent Exclusive'

WHEN 11 THEN 'UIX: Update Intent Exclusive'

WHEN 12 THEN 'BU: Used by bulk operations'

WHEN 13 THEN 'RangeS_S: Shared Key-Range and Shared Resource lock'

WHEN 14 THEN 'RangeS_U: Shared Key-Range and Update Resource lock'

WHEN 15 THEN 'RangeI_N: Insert Key-Range and Null Resource lock'

WHEN 16 THEN 'RangeI_S: Key-Range Conversion lock - overlap of RangeI_N and S locks'

WHEN 17 THEN 'RangeI_U: Key-Range Conversion lock - overlap of RangeI_N and U locks'

WHEN 18 THEN 'RangeI_X: Key-Range Conversion lock - overlap of RangeI_N and X locks'

WHEN 19 THEN 'RangeX_S: Key-Range Conversion lock - overlap of RangeI_N and RangeS_S locks'

WHEN 20 THEN 'RangeX_U: Key-Range Conversion lock - overlap of RangeI_N and RangeS_U locks'

WHEN 21 THEN 'RangeX_X: Exclusive Key-Range and Exclusive Resource lock (WHEN updating a key in a range)'

END RequestMode,

CASE req_status

WHEN 1 THEN 'Granted'

WHEN 2 THEN 'Converting'

WHEN 3 THEN 'Waiting'

END ReqStatus,

CASE req_ownertype

WHEN 1 THEN 'Transaction'

WHEN 2 THEN 'Cursor'

WHEN 3 THEN 'Session'

WHEN 4 THEN 'ExSession'

END ReqOwner

FROM master..sysprocesses, master..syslockinfo

WHERE spid = req_spid

ORDER BY 1, 2, 3