use master
go

if exists (select * from sysobjects where id = 
object_id('dbo.sp_spid') and sysstat & 0xf = 4)
drop procedure dbo.sp_spid
GO

CREATE PROC sp_spid  @id int=null , @debug tinyint=0 as

-- 
-----------------------------------------------------------------------  
------
--
-- Object Name: master.dbo.spid
-- Author: AENDER
-- Created: 24Aug1997
-- Description: Display details for specified spid
-- Return Codes: -1 Spid is not active
--
-- NOTES: This works fine on SQL/Server 6.50 - 6.50.240
-- and 6.50 - 6.50.258
-- But should work fine from version 6 upwards
-- 
-- History:
-- Date Name Version Description
--
-- 
-----------------------------------------------------------------------  
------


SET NOCOUNT ON

-- ---------------------------
-- Ensure Spid exists
-- ---------------------------

   DECLARE @suid varchar(8) , @waittype binary(2)
   SELECT @suid = convert(varchar(8),suid),
   @waittype = waittype
   FROM master..sysprocesses
   WHERE spid=@id

   IF @suid = null
     RETURN -1

-- -------------------------------------------------------------
-- Display Sysprocess info
-- Extract what is most usefull for your site here.
--   We have integrated security and are more interested in
--   NT login name rather than HOST name or LOGIN names
-- -------------------------------------------------------------

   DECLARE @w1 varchar(255), @w2 varchar(255)
   SELECT @w1='Details for spid ' + convert(varchar(30),@id) + ' at ' 
+ convert(varchar(30),getdate())
   PRINT @w1
   SELECT @w1=replicate('=',datalength(@w1))  /* Underline title */
   PRINT @w1
   PRINT ' '

   SELECT @w1=
' Status........... ' + status + char(10) +
' HostName......... ' + hostname + char(10) +
' Programe Name.... ' + program_name + char(10) +
' Command.......... ' + cmd + char(10) +
' CPU.............. ' + convert(varchar(30),cpu) + char(10) +
' Physical IO...... ' + convert(varchar(30),physical_io) + char(10) 
,
@w2= 
' BLOCKED.......... ' + convert(varchar(30),blocked) + char(10) +
' NT User.......... ' + nt_username + char(10) +
' last_batch ...... ' + convert(varchar(30),last_batch)+  char(10) 
+
' Running For ..... ' + 
convert(varchar(30),datediff(minute,last_batch,getdate()) ) + ' 
minutes' +  char(10) +
' Wait Type ....... ' + convert(varchar(30),convert(int,waittype))
   FROM master..sysprocesses
   WHERE spid=@id

   PRINT @w1
   PRINT @w2

-- -----------------------------------------------------------------
-- Convert waittype into English
-- These descriptions were sourced from Technet article Q162361
-- -----------------------------------------------------------------


  IF convert(int,@waittype) <> 0
    BEGIN
PRINT ' '
DECLARE @waitdesc varchar(255) 
SELECT @waitdesc=
CASE
WHEN @waittype = 0x8006 THEN ' **WAITTYPE 0x800  -- Waiting on 
network I/O completion'
WHEN @waittype = 0x8011 THEN ' **WAITTYPE 0x8011 -- Waiting on 
buffer resource lock (shared) request'
WHEN @waittype = 0x81   THEN ' **WAITTYPE 0x81   -- Waiting on 
writelog'
WHEN @waittype = 0x0020 THEN ' **WAITTYPE 0x0020 -- Waiting on 
buffer in I/O'
WHEN @waittype = 0x0005 THEN ' **WAITTYPE 0x0005 -- Waiting on 
exclusive page lock'
WHEN @waittype = 0x13   THEN ' **WAITTYPE 0x13   -- Waiting on 
buffer resource lock (exclusive) request'
WHEN @waittype = 0x8001 THEN ' **WAITTYPE 0x8001 -- Waiting on 
exclusive table lock'
WHEN @waittype = 0x8007 THEN ' **WAITTYPE 0x8007 -- Waiting on 
update page lock'
WHEN @waittype = 0x8005 THEN ' **WAITTYPE 0x8005 -- Waiting on 
exclusive page lock'
WHEN @waittype = 0x8003 THEN ' **WAITTYPE 0x8003 -- Waiting on 
exclusive intent lock'
WHEN @waittype = 0x6    THEN ' **WAITTYPE 0x6    -- Waiting on 
shared page lock'
WHEN @waittype = 0x8006 THEN ' **WAITTYPE 0x8006 -- Waiting on 
shared page lock'
WHEN @waittype = 0x23   THEN ' **WAITTYPE 0x23   -- Waiting on 
buffer being dumped'
WHEN @waittype = 0x5    THEN ' **WAITTYPE 0x5    -- Waiting on 
exclusive page lock'
WHEN @waittype = 0x0013 THEN ' **WAITTYPE 0x0013 -- Waiting on 
buffer resource lock (exclusive) request'
WHEN @waittype = 0x0022 THEN ' **WAITTYPE 0x0022 -- Waiting on 
buffer being dirtied'
END
PRINT @waitdesc
   END

-- 
-----------------------------------------------------------------------  
------
-- Get input buffer and PSS
-- The use of DBCC PSS Is mentioned in Technet article Q162361.
-- PSS is also documented on several SYBASE user groups.
-- The systax of the command is
-- pss( suid, spid, printopt = { 1 | 0 } )
--
-- You can obtain this via the following code (it works for all DBCC 
commands)
--
-- DBCC traceon(3604)  /* trace output to client */
-- dbcc help(pss)
-- DBCC traceoff(3604)
--
-- I have encounted no problems with the pss (suid,spid,0) form of the 
command.
-- If you miss out any parameters or use printopt=1 you get a LOT of 
output.
-- 
-----------------------------------------------------------------------  
-------

-- We use the /S @@Servername option because one of our sersers runs 
4.2 and 6.5 with different named pipes

   DECLARE @cmd varchar(255)
   CREATE TABLE #details (id int identity,dbcc_op varchar(255) null)
   SELECT @cmd= 'ISQL /S'+ @@servername + ' /Usa /P /Q"dbcc 
inputbuffer('+ convert(varchar(10),@id ) + ') DBCC traceon(3604) DBCC 
PSS (' +@suid + ',' + convert(varchar(10),@id ) + ',0) dbcc 
traceoff(3604)" /dmaster /w255'
   INSERT INTO #details EXEC master..xp_cmdshell @cmd

   IF @debug>0 select * from #details

-- ----------------------------------------------------------
-- Extract PSS detail
-- The Fields I have chosen are worked out by trial & error
-- ----------------------------------------------------------

-- pcurdb=   Procedure Current Database ?

   DECLARE @db int
   SELECT @db=convert(int,substring(dbcc_op,charindex('pcurdb=',dbcc_op  
)+7,3)) 
   FROM #details
   WHERE dbcc_op like '%pcurdb=%'

-- plastprocid= Procedure ID of last procedure to be executed ?

   DECLARE @pid int
   SELECT @pid=convert(int,substring(dbcc_op,charindex('plastprocid=',d  
bcc_op)+12,11)) 
   FROM #details
   WHERE dbcc_op like '%plastprocid=%'

-- pline=  The current line of the procedure ?

   DECLARE @pline int
   SELECT @pline=convert(int,substring(dbcc_op,charindex('pline=',dbcc_  
op)+6,6)) 
   FROM #details
   WHERE dbcc_op like '%pline=%'

-- xactcnt= Transaction count ?  (@@trancount?)

   DECLARE @xact varchar(10)
   SELECT @xact=convert(varchar(10),substring(dbcc_op,charindex('xactcn  
t=',dbcc_op)+8,2)) 
   FROM #details
   WHERE dbcc_op like '%xactcnt=%'

-- pstat=  Process status codes ?

   DECLARE @pstat varchar(255) ,@pss1 int ,@pss2 int
   SELECT @pss1=id-1,@pstat=dbcc_op
   FROM #details
   WHERE dbcc_op like '%pstat=%'

   SELECT @pss2=id-1
   FROM #details
   WHERE dbcc_op like '%PHDR:%'

-- ----------------------------------------------------------------
-- Decifer @pstats
-- These descriptions were sourced from Technet article Q162361
-- ----------------------------------------------------------------

   PRINT ' '

   IF charindex('0x4000 ',@pstat) > 0 PRINT ' **PSTAT    0x4000 -- 
Delay KILL and ATTENTION signals if inside a critical section'
   IF charindex('0x2000 ',@pstat) > 0 PRINT ' **PSTAT    0x2000 -- 
Process is being killed'
   IF charindex('0x800 ',@pstat) > 0 PRINT ' **PSTAT    0x800  -- 
Process is in backout, thus cannot be chosen as deadlock victim'
   IF charindex('0x400 ',@pstat) > 0 PRINT ' **PSTAT    0x400  -- 
Process has received an ATTENTION signal, and has responded by 
raising an internal exception'
   IF charindex('0x100 ',@pstat) > 0 PRINT ' **PSTAT    0x100  -- 
Process in the middle of a single statement xact'
   IF charindex('0x80 ',@pstat) > 0 PRINT ' **PSTAT    0x80   -- 
Process is involved in multi-db transaction'
   IF charindex('0x8 ',@pstat) > 0 PRINT ' **PSTAT    0x8    -- 
Process is currently executing a trigger'
   IF charindex('0x2 ',@pstat) > 0 PRINT ' **PSTAT    0x2    -- 
Process has received KILL command'
   IF charindex('0x1 ',@pstat) > 0 PRINT ' **PSTAT    0x1    -- 
Process has received an ATTENTION signal'
   IF charindex('0x4000,',@pstat) > 0 PRINT ' **PSTAT    0x4000 -- 
Delay KILL and ATTENTION signals if inside a critical section'
   IF charindex('0x2000,',@pstat) > 0 PRINT ' **PSTAT    0x2000 -- 
Process is being killed'
   IF charindex('0x800,',@pstat) > 0 PRINT ' **PSTAT    0x800  -- 
Process is in backout, thus cannot be chosen as deadlock victim'
   IF charindex('0x400,',@pstat) > 0 PRINT ' **PSTAT    0x400  -- 
Process has received an ATTENTION signal, and has responded by 
raising an internal exception'
   IF charindex('0x100,',@pstat) > 0 PRINT ' **PSTAT    0x100  -- 
Process in the middle of a single statement xact'
   IF charindex('0x80,',@pstat) > 0 PRINT ' **PSTAT    0x80   -- 
Process is involved in multi-db transaction'
   IF charindex('0x8,',@pstat) > 0 PRINT ' **PSTAT    0x8    -- 
Process is currently executing a trigger'
   IF charindex('0x2,',@pstat) > 0 PRINT ' **PSTAT    0x2    -- 
Process has received KILL command'
   IF charindex('0x1,',@pstat) > 0 PRINT ' **PSTAT    0x1    -- 
Process has received an ATTENTION signal'

   if @debug >2 select db=@db,pid=@pid,pl=@pline,xc=@xact

-- ---------------------------
-- Display TSQL info
-- ---------------------------

   DECLARE @ipb varchar(255) Select @ipb=dbcc_op from #details where 
id=7
   DECLARE @dbn varchar(255) Select @dbn=name from 
master..sysdatabases where dbid=@db

   PRINT ' '
   SELECT @cmd=' Input buffer.....' + @ipb
   PRINT @cmd

   SELECT @cmd=' Database......... ' + @dbn
   PRINT @cmd

   SELECT @cmd=' @@trancount...... ' + @xact
   PRINT @cmd

-- 
----------------------------------------------------------------------  

-- Display locks
-- I'm not sure when sp_lock2 arrived but it shows locks in more 
detail
-- 
----------------------------------------------------------------------  


   DECLARE @locks int SELECT @locks = count(*) from master..syslocks 
where spid=@id
   IF @locks > 0
     BEGIN
        SELECT @cmd=' Locks held....... ' + 
convert(varchar(30),@locks)
        PRINT @cmd

PRINT ' '
PRINT '----------------------------- Lock Details 
------------------------------------'
PRINT ' '

IF EXISTS (select * from master..sysobjects where name = 'sp_lock2' 
and type ='p')
select @cmd='sp_lock2 ' + convert(varchar(30),@id)
  ELSE
select @cmd='sp_lock ' + convert(varchar(30),@id)
EXEC (@cmd)

PRINT '---------------------------------------------------------------  
----------------'
PRINT ' '
   END

-- ---------------------------
-- Extract current proc name
-- ---------------------------

   CREATE TABLE #t1(work_op varchar(255) null)
   SELECT @cmd='Select name from ' + @dbn + '..sysobjects where id=' + 
convert(varchar(30),@pid)
   INSERT INTO #t1 EXEC (@cmd)

   DECLARE @cur_proc varchar(255) select @cur_proc=work_op from #t1
   IF Ltrim(@cur_proc) <> null
    BEGIN

PRINT ' '
PRINT '------------------- Currently running Stored Procedure 
------------------------'
PRINT ' '
SELECT @cmd='   Line:' + convert(varchar(30),@pline) + ' (approx.) 
of ' + upper(@cur_proc)
PRINT @cmd

PRINT ' '
PRINT ' '

-- -------------------------------
-- Extract source of current proc
-- -------------------------------

SELECT @cmd='Select text,number from ' + @dbn + '..syscomments where 
id=' + convert(varchar(30),@pid)
CREATE TABLE #t2 (text varchar(255) , number int)
INSERT  INTO #t2 EXEC (@cmd)

-- ------------------------------------------
-- split into lines (still needs some work)
-- -----------------------------------------

declare @crlf char(2) select @crlf=char(13) + char(10)

create table #t3 (x char(3) null ,Line smallint identity, Content 
varchar(255) null)

set nocount on
declare lines cursor for select text from #t2 order by number
open lines
declare @text varchar(255) , @endofline tinyint ,@line varchar(255)
while 1=1
  begin
fetch next from lines into @text
if @@fetch_status <> 0
break
while charindex(@crlf,@text) > 0
  begin
select @line=@line + substring(@text,1,charindex(@crlf,@text)-1) 
insert into #t3(Content) select @line 
select 
@text=substring(@text,charindex(@crlf,@text)+2,datalength(@text)-charin  
dex(@crlf,@text)-1) ,@line=null
END
select @line=@text
end
insert into #t3(Content) select @line
deallocate lines

-- -------------------------------
-- Highlight current line
-- -------------------------------

update #t3 set x='>>>' where line=@pline

-- -------------------------------
-- Dispaly sp
-- -------------------------------

select isnull(x,''),Line,Content=isnull(content,'') from #t3 order by 
line

   END

-- -------------------------------
-- Display Output buffer
-- One day this might be usefull
-- -------------------------------

   PRINT ' '
   PRINT '--------------------------- OUTPUT BUFFER 
----------------------------------'
   DBCC OUTPUTBUFFER (@id)

-- -------------------------------
-- Display PSS
--
-- -------------------------------

   PRINT ' '
   PRINT '-----------------  Process Slot Structure 
--------------------------'
   SELECT ' '=dbcc_op from #details where id between @pss1 and @pss2


GO