USE master
GO
-------------------------------
-- Nicolas Souquet - 05/07/2012
-------------------------------
CREATE PROCEDURE sp__processes_locks
  @_spid int = NULL
AS
BEGIN
  SET NOCOUNT ON  
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
  SELECT    S.host_name
      , S.login_name
      , CASE  
        WHEN S.program_name LIKE 'Microsoft SQL Server Management Studio%' THEN 'SSMS'
        WHEN S.program_name LIKE 'LiteSpeed for SQL Server%' THEN 'LiteSpeed'
        WHEN S.program_name = 'Microsoft SQL Server Analysis Services' THEN 'SSAS'
        ELSE S.program_name
      END AS program
      , D.name AS database_name
      , SQLT.objectid
      , TL.request_session_id
      , TL.resource_type
      , TL.resource_subtype
      , TL.request_mode
      , TL.request_type
      , TL.request_status
      , COUNT(*) AS lock_count
      , CASE
        WHEN TL.resource_type = 'OBJECT' THEN OBJECT_NAME(TL.resource_associated_entity_id)
        WHEN TL.resource_type IN ('KEY', 'PAGE', 'RID')
          THEN
          (
            SELECT  OBJECT_NAME(object_id)
            FROM  sys.partitions AS P
            WHERE  P.hobt_id = TL.resource_associated_entity_id
          )
        ELSE TL.resource_type  
      END AS requested_object_name
      , R.wait_type
      , R.wait_time
      , R.last_wait_type
      , R.plan_handle
      , R.cpu_time / 1000 AS CPU
      , R.reads AS reads
      , R.writes AS writes
      , R.open_transaction_count AS nbtran
  FROM    sys.dm_tran_locks AS TL
  INNER JOIN  sys.databases AS D
        ON TL.resource_database_id = D.database_id
  LEFT JOIN  sys.dm_exec_requests AS R
        ON TL.request_session_id = R.session_id
  INNER JOIN  sys.dm_exec_sessions AS S
        ON R.session_id = S.session_id
  CROSS APPLY  sys.dm_exec_sql_text(R.sql_handle) SQLT
  WHERE    TL.request_session_id >= 50
  AND    TL.request_session_id <> @@SPID
  AND    (
        @_spid IS NULL
        OR TL.request_session_id = @_spid
      )
  GROUP BY  S.host_name
      , S.login_name
      , S.program_name
      , D.name
      , TL.request_session_id
      , TL.resource_type
      , TL.resource_subtype
      , TL.request_mode
      , TL.request_type
      , TL.request_status
      , TL.resource_type
      , SQLT.objectid
      , TL.resource_associated_entity_id
      , R.wait_type
      , R.wait_time
      , R.last_wait_type
      , R.plan_handle
      , R.cpu_time
      , R.reads
      , R.writes
      , R.open_transaction_count
  ORDER BY  TL.request_session_id
END
GO
 
EXEC sp_ms_marksystemobject 'sp__processes_locks'