-- Stored Procedure: dbo.GetLocks
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLocks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLocks]
GO




CREATE PROCEDURE [dbo].[GetLocks] 
   @SPID int = null                        -- in case you want to see info for a particular process only 
 , @InvolvedInBlocks varchar(5) = 'False'  -- if true only get info for locks involved in blocking
 , @IncludeSQL varchar(5) = 'True'         -- if true attempt to get fn_get_sql and inputbuffer for each
 , @MaxToReturn int = 200                  -- max number of lock info rows to return to client
AS
/*******************************************************************************************************
* admin.dbo.GetLocks 
* Creator:       Billw
* Date:          10-21-2003
*
* Description:   gel lock details
*
* Usage:
      EXECUTE admin.dbo.GetLocks 
      EXECUTE admin.dbo.GetLocks null,'true','false'
      EXECUTE admin.dbo.GetLocks 55,'true','false'
      EXECUTE admin.dbo.GetLocks 55,'true','true'
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*          bw                 07-08-04 set based syslockinfo query, elim page and extent lock info          
*          bw                 07-15-04 improve sorting and labling, modify parameter order         
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @pid int
    , @SQLStr nvarchar(200) 
    , @Length int

---------------------------------------------
-- create temp tables
---------------------------------------------
create table #info
   (Id int identity(1,1) primary key
   , SPID int
   , rsc_dbid int
   , ObjectID varchar(10)
   , Object varchar(256)
   , IndexId tinyint
   , Type varchar(4)
   , Resource nvarchar(16)
   , Mode varchar(8)
   , Status varchar(5)
   , Xaction bigint
   , BlockedBy int)

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------

set rowcount @MaxToReturn

insert #info
   (SPID
   , rsc_dbid
   , ObjectID
   , IndexId
   , Type
   , Resource
   , Mode
   , Status
   , Xaction
   , BlockedBy)
select i.req_spid
   , i.rsc_dbid
   , i.rsc_objid
   , i.rsc_indid
   , left(v.name,4)
   , left(i.rsc_text,16)
   , left(u.name, 8)
   , left(x.name, 5)
   , i.req_transactionID
   , p.blocked
from master.dbo.sysprocesses p with(nolock)
join master.dbo.syslockinfo i with(nolock)
on p.spid = i.req_spid
join master.dbo.spt_values v with(nolock)
on i.rsc_type = v.number 
and v.type = 'LR'
and v.name in ('TAB', 'KEY','IDX','FIL','RID','APP')
join master.dbo.spt_values x with(nolock)
on i.req_status = x.number
and x.type = 'LS'
join master.dbo.spt_values u with(nolock)
on i.req_mode + 1 = u.number 
and u.type = 'L'
where i.rsc_objid > 0 
and p.spid <> @@SPID
and (p.blocked <> 0
  or p.spid in (select blocked from master.dbo.sysprocesses with(nolock) where blocked > 0)
  or @InvolvedInBlocks = 'False')
and (@spid is null or p.spid = @spid)
order by 
 case v.name 
  when 'TAB' then 0
  when 'IDX' then 1
  when 'APP' then 2
  when 'FIL' then 3
  when 'KEY' then 4
  when 'RID' then 5
  else 6 end 
 , case  
    when u.name like 'Sch%' then 1
    when u.name like '%X%' then 2
    when u.name like '%U%' then 3         
    else 4 end -- shared locks 
 , case x.name
    when 'WAIT' then 1
    else 0 end
 , db_name(rsc_dbid)
 ,rsc_objid
 ,req_transactionID
 , i.req_status
 
set rowcount 0

exec sp_execresultset 'select distinct
''update i 
     set Object = db_name(i.rsc_dbid) + char(46) + u.name + char(46) 
      + case when (charindex(char(35),o.name) = 1) and (charindex(char(95) + char(95) + char(95),o.name) > 2)
         then  substring(o.name,1,charindex(char(95) + char(95) + char(95),o.name) - 1)
         else o.name end
  from #info i 
  join '' + db_name(rsc_dbid) + ''.dbo.sysobjects o with(nolock)
  on i.ObjectId = o.id
  left join '' + db_name(rsc_dbid) + ''.dbo.sysusers u with(nolock)
  on o.uid = u.uid
  where i.rsc_dbid = '' + cast(rsc_dbid as varchar(10)) + ''
  and i.ObjectId = '' + cast(ObjectId as varchar(10))
from #info
where Objectid > 0'

if exists(select 1 from #info)
 begin
  select @Length = max(datalength(ltrim(isnull(Object,0)))) + 1 from #info

  select 'Locks held'

   set @SQLStr = 
    'select SPID
     , cast(Object as varchar(' + cast(@length as varchar(10)) + '))
     , IndexId [Index]
     , Type
     , Resource
     , Mode
     , Status
     , Xaction
    from #info' 

  exec sp_executesql @SQLStr

  if @IncludeSQL = 'True'
   begin
    select @pid = min(spid) from #info where ObjectId > 0
    while @pid is not null
     begin
      exec admin.dbo.GetSQL @pid, 1 
      select @pid = min(spid) from #info where spid > @pid and ObjectId > 0  
     end 
   end
 end
else
 begin
  if @InvolvedInBlocks = 'True'
   select 'No lock data available. The blocking condition has probably cleared.'
  else 
   select 'No lock data available.'   
 end

RETURN



GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO