New SP_WHO2

A long time ago, I was given a stored proc called "SP_WHOM" which said it was created by "MFrank". SP_WHOM has been a staple in my collection of utilities I use to debug issues on my SQL Servers. I never paid it much mind, until recently, when the thought occurred to me that I knew how to get all of the provided data from SP_WHOM, in an easier way, and I could add some beneficial new fields in the process. So I set out to try to design my own SP_WHOM, so I took at look at the fields provided in the DMV sys.sysprocess All the fields I wanted were in there; so I decided to design my own and this is what I came up with:

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*******************************
** Created By: Eric Zierdt **
** ericzierdt@gmail.com **
** ericemployed.blogspot.com **
*******************************/
CREATE PROCEDURE [dbo].[sp_whom]
AS

SET NOCOUNT ON

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#RawData'))
BEGIN
 DROP TABLE #RawData
END

SELECT SPID
  ,UPPER(status) AS Status
  ,LogiName
  ,HostName
  ,CASE blocked
   WHEN 0 THEN ''
   ELSE CAST(blocked AS VARCHAR(5))
  END AS BlockedBy
  ,Open_Tran
  ,DB_NAME(S.dbid) AS DBName
  ,CMD AS Command
  ,Last_Batch
  ,DATEDIFF(mi,last_batch,GETDATE()) AS RunTimeMins
  ,DATEDIFF(ss,last_batch,GETDATE()) AS RunTimeSecs
  ,CPU AS CPU_Time
  ,Physical_IO AS DiskIO
  ,Program_Name
  ,ST.text AS SQL_Text
INTO #RawData
FROM    sys.sysprocesses AS S
CROSS APPLY sys.dm_exec_sql_text(S.sql_handle) ST

SELECT *
FROM #RawData R
WHERE   spid <> @@SPID
  AND status NOT IN ( 'background', 'sleeping' )
UNION
SELECT *
FROM #RawData AS RD
WHERE SPID IN (
   SELECT BlockedBy
   FROM #RawData AS RD2
   WHERE RD2.BlockedBy <> ''
  )
ORDER BY SPID

DROP TABLE #RawData

GO

Why are you doing a UNION you might ask? I'm doing it because I want to display only those SPIDS which are not set to "Sleeping" or "Background" status, however...if a SPID is blocked by another SPID, I'd like to display the blocking SPID as well. If I didn't do this and someone opens a transaction, updates records and doesn't close the transaction, their SPID's status will be Sleeping, so you will see that any spid trying to access the record(s) are blocked, but you won't know anything about the SPID doing the blocking.

I'd love feedback, is there something I should add to this to make it more useful? Please follow my blog and leave me some feedback.

P.s. because this is being placed in the master database and has the sp_ prefix you can call this from any database by just executing: "sp_whom" no need for exec or master.. or anything.