Get processes blocking each other and the queries being run

GetProcessLocks.sql

CREATE TABLE #tempHAL (
	SPID NVARCHAR(255),
	Status NVARCHAR(255),
	Login NVARCHAR(255),
	HostName NVARCHAR(255),
	BlkBy NVARCHAR(255),
	DBName NVARCHAR(255),
	Command NVARCHAR(MAX),
	CPUTime NVARCHAR(255), 
	DiskIO NVARCHAR(255),
	LastBatch NVARCHAR(255),
	ProgramName NVARCHAR(255),
	SPID2 NVARCHAR(255),
	REQUESTID NVARCHAR(255)
);

INSERT INTO #tempHAL
EXEC SP_WHO2

-- Select blocked PIDs and blockers

DECLARE @PID int, @sql NVARCHAR(MAX)
DECLARE @DBCCEventData AS TABLE (EventType NVARCHAR(255), [Parameters] int, EventInfo NVARCHAR(MAX), PID int)
DECLARE @EventData AS TABLE (EventType NVARCHAR(255), [Parameters] int, EventInfo NVARCHAR(MAX), PID int)
DECLARE csrBlk CURSOR FOR SELECT DISTINCT PID.SPID FROM #tempHAL PID INNER JOIN #tempHAL BLK
	ON PID.SPID = BLK.BlkBy OR PID.BlkBy != '  .'

OPEN csrBLK

FETCH csrBlk INTO @PID

WHILE @@FETCH_STATUS = 0
BEGIN
	DELETE FROM @DBCCEventData

	SET @sql = 'DBCC INPUTBUFFER (' + CAST(@PID AS NVARCHAR(10)) + ')'

	INSERT INTO @DBCCEventData (EventType, [Parameters], EventInfo)
	EXEC (@sql)

	UPDATE @DBCCEventData SET PID = @PID
	INSERT INTO @EventData SELECT * FROM @DBCCEventData

	FETCH csrBlk INTO @PID
END

CLOSE csrBLK

DEALLOCATE csrBLK

SELECT DISTINCT PID.*, S.EventInfo FROM #tempHAL PID INNER JOIN #tempHAL BLK
	ON PID.SPID = BLK.BlkBy OR PID.BlkBy != '  .' LEFT OUTER JOIN @EventData S ON PID.SPID = CAST(S.PID AS VARCHAR(10))

GO

DROP TABLE #tempHAL
GO