-- Stored Procedure: dbo.GetSpid
-- 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].[GetSpid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSpid]
GO


CREATE PROCEDURE [dbo].[GetSpid] 
 @Spid int 
AS
/*******************************************************************************************************
* admin.dbo.GetSpid
* Creator:       Billw
* Date:          1-13-2003
*
* Description:   get sysprocesses info, locks and buffer details for a specified spid
*
* Usage:
      EXECUTE admin.dbo.GetSpid 54
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @id int
    , @OBJStr nvarchar(1000)
    , @Object varchar(30)
    , @GetSQLStr nvarchar(1000)
    , @Handle varbinary(20) 
    , @sql varchar(7500)
    , @buffer varchar(255)

---------------------------------------------
-- create temp tables
---------------------------------------------
declare @lockinfo table 
   (Id int identity(1,1) primary key
   , DBName varchar(18)
   , ObjectID varchar(10)
   , Object varchar(30)
   , IndexId tinyint
   , Type varchar(4)
   , Resource nvarchar(16)
   , Mode varchar(8)
   , Status varchar(5)
   , Xaction bigint)

create table #inputbuffer
     (EventType varchar(20)
     , Parameter int
     , EventInfo varchar(255))

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

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

select * 
from master.dbo.sysprocesses 
where spid = @spid

insert @lockinfo
   (DBName
   , ObjectID
   , IndexId
   , Type
   , Resource
   , Mode
   , Status
   , Xaction)
select left(db_name(i.rsc_dbid),18)
   , 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
from master.dbo.syslockinfo i with(nolock)
join master.dbo.spt_values v with(nolock)
on i.rsc_type = v.number 
and v.type = 'LR'
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 i.req_spid = @SPID
order by db_name(rsc_dbid)
    ,rsc_objid
    ,req_transactionID
    , case x.name when 'WAIT' then 1 else 0 end
 
-- try not to fail is spid is executing parallel 
select @GetSQLStr = 'select @sql = replace(replace(replace(substring(text, ' + 
          '(' + cast(p.stmt_start as varchar(10)) + ' + 2)/2,CASE ' + cast(p.stmt_end as varchar(10)) + ' ' + 
                     'WHEN -1 THEN (datalength(text)) ' + 
                     'ELSE (' + cast(p.stmt_end as varchar(10)) + ' - ' + cast(p.stmt_start as varchar(10)) + ' + 2)/2 ' + 
                     'END) ,char(13),char(32)),char(10),char(32)),char(9),char(32)) ' + 
          'from ::fn_get_sql(@Handle)'
 , @Handle = p.sql_handle
from (select top 1 sql_handle, stmt_start, stmt_end 
      from master.dbo.sysprocesses 
      where spid = @spid) p
where stmt_end > 0

if @GetSQLStr is not null
  exec sp_executesql @GetSQLStr, N'@Handle varbinary(20), @sql varchar(7500) OUTPUT', @Handle, @sql OUTPUT       

if @sql is null 
 set @sql = '(no statement found in cache)'

insert #inputbuffer exec sp_executesql N'dbcc inputbuffer(@spid)',N'@spid int',@spid    
select @buffer = replace(replace(EventInfo, char(13), ' '), char(10), ' ') -- get rid of CR and LF
from #inputbuffer 
if @buffer is null 
 set @buffer = 'DBCC INPUTBUFFER output not available'

select 'spid: ', @SPID , getdate(), char(13) + char(10) 
     + 'currently executing statement: ' + left(@sql,255) + char(13) + char(10)      + 'input buffer: ' + @buffer

select @id = min(id) from @lockinfo where ObjectId > 0 and Object is null
while @id is not null
 begin
  select @ObjStr = null

  select @ObjStr = 'use ' + DBName + ' select @Object = left(object_name(' + cast(ObjectId as varchar(10)) + '),30)'
  from @lockinfo 
  where id = @id

  if @ObjStr is not null
   exec sp_executesql @ObjStr, N'@Object varchar(30) OUTPUT', @Object OUTPUT       
  else
   set @Object = 'unknown' 

  update i2
  set Object = @Object
  from @lockinfo i1
  join @lockinfo i2
  on i1.ObjectId = i2.ObjectId
  where i1.Id = @id
    
  select @id = min(id) from @lockinfo where ObjectId > 0 and Object is null  and id > @id
 end

if exists (select 1 from @lockinfo)
 begin
  select 'Locks held'
  select cast(DBName + '..' + Object as varchar(40))
     , IndexId [Index]
     , Type
     , Resource
     , Mode
     , Status
     , Xaction
   from @lockinfo 
 end
else
 select 'No lock info available.'   


RETURN





GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO