MSSQL - Find Blocking SQL

The script below used to find the SQL which is in blocking.


-- use command below to find the spid of the block sql
SP_WHO2

-- Another way to find blocked processSELECT    spid,
    status,
    loginame=SUBSTRING(loginame,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)


-- use the spid to list the SQL
DECLARE @handle binary(20)

SELECT @handle = max(sql_handle)
FROM master..sysprocesses
WHERE spid = @SPID

-- print SQL
SELECT [text] 
FROM ::fn_get_sql(@handle)