SQL Server Locks

A better alternative to sp_lock with information about the locks in the current database:


SELECT
  li.req_spid As "Session ID",
  o.[name] AS "Object Name",
  i.[name] AS "Index Name",
  v.[name] As "Lock Type",
  u.[name] As "Lock Mode"
FROM
  master..syslockinfo AS li
  INNER JOIN master..spt_values AS v ON li.[rsc_type] = v.[number] AND v.[type] = 'LR'
  INNER JOIN master..spt_values AS u ON li.[req_mode] + 1 = u.[number] AND u.[type] = 'L'
  INNER JOIN sysobjects AS o ON o.[id] = li.[rsc_objid]
  LEFT OUTER JOIN sysindexes AS i ON i.[id] = li.[rsc_objid] AND i.[indid] = li.[rsc_indid]
WHERE
  li.rsc_dbid = db_id()
ORDER BY
  o.[name],
  i.[name],
  v.[name] desc