SQL sp_leadblocker

sp_leadblocker

use master
SET QUOTED_IDENTIFIER OFF
go

if exists (SELECT * from sysobjects where name='sp_leadblocker' and type='P')
	DROP PROC sp_leadblocker
go

CREATE PROCEDURE sp_leadblocker
AS
IF EXISTS
    (SELECT * FROM master.dbo.sysprocesses
    WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
    SELECT 
        spid, status, loginame=SUBSTRING(SUSER_NAME(suid), 1, 12),
        hostname=substring(hostname, 1, 12),
            blk=CONVERT(char(3), blocked),
        dbname=substring(db_name(dbid),1,10),cmd, waittype
        FROM master.dbo.sysprocesses
        WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
            AND blocked=0
ELSE
SELECT "No blocking processes found!"
go