sp_lock_detail

SQL Serverにおいてロック状況の確認をするストアド

毎回調べているので自分用メモ

USE [master]
GO
/****** オブジェクト:  StoredProcedure [dbo].[sp_lock_detail]    スクリプト日付: 06/01/2009 19:47:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_lock_detail]
 @spid1 int = NULL,    /* server process id to check for locks */
 @spid2 int = NULL     /* other process id to check for locks */
AS

SET NOCOUNT ON
/*
**  Show the locks for both parameters.
*/
 IF @spid1 IS NOT NULL
 BEGIN
  SELECT CONVERT (SMALLINT, req_spid) AS spid,
       db_name(rsc_dbid) AS dbid,
       object_name(rsc_objid) AS ObjId,
       rsc_indid AS IndId,
       SUBSTRING (v.name, 1, 4) AS Type,
       SUBSTRING (rsc_text, 1, 16) AS Resource,
       SUBSTRING (u.name, 1, 8) AS Mode,
       SUBSTRING (x.name, 1, 5) AS Status

  FROM master.dbo.syslockinfo,
       master.dbo.spt_values v,
       master.dbo.spt_values x,
       master.dbo.spt_values u

  WHERE master.dbo.syslockinfo.rsc_type = v.number
             and v.type = 'LR'
             and master.dbo.syslockinfo.req_status = x.number
             and x.type = 'LS'
             and master.dbo.syslockinfo.req_mode + 1 = u.number
             and u.type = 'L'

             and req_spid in (@spid1, @spid2)
 END

 /*
 ** No parameters, so show all the locks.
 */
 ELSE
 BEGIN
  SELECT CONVERT (SMALLINT, req_spid) AS spid,
       db_name(rsc_dbid) AS dbid,
       object_name(rsc_objid) AS ObjId,
       rsc_indid AS IndId,
       SUBSTRING (v.name, 1, 4) AS Type,
       SUBSTRING (rsc_text, 1, 16) AS Resource,
       SUBSTRING (u.name, 1, 8) AS Mode,
       SUBSTRING (x.name, 1, 5) AS Status

  FROM master.dbo.syslockinfo,
       .dbo.spt_values v,
       .dbo.spt_values x,
       .dbo.spt_values u

  WHERE master.dbo.syslockinfo.rsc_type = v.number
          and v.type = 'LR'
          and master.dbo.syslockinfo.req_status = x.number
          and x.type = 'LS'
          and master.dbo.syslockinfo.req_mode + 1 = u.number
          and u.type = 'L'
  ORDER BY spid
 END

 RETURN (0) -- sp_lock

引用サイト
http://www.atmarkit.co.jp/fnetwork/rensai/sql28/sql1.html