I've always liked SP_WHO2. It is a simple command and has been around forever (well at least since 2000). Before SQL Server 2005 it was my go to command any time I thought I had blocking issues or needed quick information on a given SPID. I still use it, in spite of the additional DMVs that have largely superseded it because it is one quick command, it works on any version and I don't have to remember anything in the heat of battle.
But it is old and has some flaws. I've had some scripts over the year to address those flaws and I have tried a number of DMVs, but I just never felt like the extra work was worth it.
So, I recently found I needed to be able to see the actual command being currently executed by a given SPID. I found a number of solutions in my search, but when I found one by Pinal Dave, I realized that with some slight modification I could make it work for all SPIDS at once if I used sys.sysprocesses. I built that, and then realized that I finally had an SP_WHO2 replacement that was worth the hassle. This gives me everything I ever wanted out of SP_WHO2 with the added benefit of being sortable and it shows the actual SQL being executed by each SPID.
So, without further ado, my own humble addition to the SQL community:
SELECT s.spid
,s.blocked
,s.status
,s.cpu
,s.memusage
,s.physical_io
,s.program_name
,s.hostname
,s.loginame
,s.last_batch
,s.waittime
,s.lastwaittype
,t.text SQLText
FROM sys.sysprocesses S
CROSS APPLY sys.dm_exec_sql_text(sql_handle) T
WHERE s.spid >= 50
ORDER BY s.blocked desc, s.spid
