blocked  and blocker  spid and details

use master

 /*create table in master database*/

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

CREATE TABLE [dbo].[BlockCheck] (
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Waittime] [int] NULL ,
[SPID_Blocked] [int] NULL ,
[LastCmd_Blocked] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dbid] [int] NULL ,
[CPU] [int] NULL ,
[Pys_IO] [int] NULL ,
[SPID_Blocker] [int] NULL ,
[LastCmd_Blocker] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName_Blocked] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName_Blocker] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgName_Blocked] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgName_Blocker] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login_Blocked] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login_Blocker] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastBatch_Blocked] [datetime] NULL ,
[LastBatch_Blocker] [datetime] NULL 
) ON [PRIMARY]
GO


/**********************/

CREATE PROCEDURE  BLOCK_CHECK

  @dbname   varchar(50)

AS



SET NOCOUNT ON


DECLARE @spid_blocked int ,
@spid_blocker int,
@cmd varchar(7000)


TRUNCATE TABLE  master..BlockCheck

   WHILE (1=1)
   BEGIN

CREATE TABLE #lock
(spid int,
dbid int,
objid int,
indid int,
type varchar(10),
resource varchar(40),
mode varchar(10),
status varchar(30))

CREATE TABLE #Blocked(
EventType varchar(100) ,
Parameters int ,
EventInfo varchar(7000))


CREATE TABLE #Blocker(
EventType varchar(100) ,
Parameters int ,
EventInfo varchar(7000))


INSERT INTO #lock EXEC sp_lock


INSERT INTO master..BlockCheck(Status, SPID_Blocked,dbid, CPU, Pys_IO, SPID_Blocker, HostName_Blocked, ProgName_Blocked, Login_Blocked, LastBatch_Blocked)
SELECT a.status, SPID, CPU,d.dbid, Physical_IO, Blocked, SUBSTRING(HostName, 1, 36), SUBSTRING(Program_Name, 1, 100), SUBSTRING(loginame, 1, 20), Last_Batch
FROM master..sysprocesses a, master..sysdatabases d
WHERE (a.blocked > 0
AND EXISTS (SELECT * FROM #lock  b WHERE  a.spid=b.spid and a.dbid=b.dbid)
 )
AND  a.SPID <> @@SPID
AND NOT EXISTS (SELECT * FROM master..BlockCheck c
WHERE a.blocked=c.SPID_Blocker
AND a.spid=c.SPID_Blocked)
AND a.dbid=d.dbid
AND d.name=@dbname


DECLARE Blocked CURSOR FAST_FORWARD FOR
SELECT SPID_Blocked FROM master..BlockCheck

OPEN Blocked

FETCH NEXT FROM  Blocked INTO @spid_blocked
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'DBCC INPUTBUFFER(' + CONVERT(varchar, @spid_blocked) + ')'

INSERT INTO #Blocked
EXEC(@cmd)

SELECT @cmd = EventInfo
FROM #Blocked

DELETE FROM #Blocked

UPDATE master..BlockCheck
SET LastCmd_Blocked = SUBSTRING(@cmd, 1, 1000)
WHERE SPID_Blocked = @spid_blocked
AND LastCmd_Blocked  IS NULL

FETCH NEXT FROM Blocked INTO @spid_blocked

END

CLOSE Blocked
DEALLOCATE Blocked


DECLARE Blocker CURSOR FAST_FORWARD FOR
SELECT SPID_Blocker FROM master..BlockCheck

OPEN Blocker

FETCH NEXT FROM Blocker INTO @spid_blocker
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'DBCC INPUTBUFFER(' + CONVERT(varchar,@spid_blocker)+ ')'

INSERT INTO #Blocker
EXEC(@cmd)

SELECT @cmd = EventInfo
FROM #Blocker

DELETE FROM #Blocker

UPDATE master..BlockCheck
SET LastCmd_Blocker = SUBSTRING(@cmd, 1, 1000)
WHERE SPID_Blocker =  @spid_blocker
AND  LastCmd_Blocker IS NULL

UPDATE master..BlockCheck
SET HostName_Blocker =hostname,
        Progname_Blocker=program_name,
Login_Blocker=loginame,
LastBatch_Blocker=Last_Batch
FROM  master..sysprocesses 
             WHERE spid= @spid_blocker
AND  LastBatch_Blocker is null



FETCH NEXT FROM Blocker INTO  @spid_blocker

END

CLOSE Blocker
DEALLOCATE Blocker


  
   DROP TABLE #Blocker
   DROP TABLE #lock
   DROP TABLE #Blocked
 
  END

WAITFOR DELAY '00:00:20'
GO