Why: During day-to-day life as a DBA I've no doubt you would have come across a situation where you need to get to the bottom what a running process is doing in SQL Server, aside from the basic sp_who2 and such I wanted to get more detail information, I wanted to know enough to give direct information in a simple form to whoever I needed to, I found this handy in particular in UAT and DEV environments as I found some of our developers were quite happy to test new functions/procedures which would quite happily run for hours locking out resource.
Function: Using sys.sysprocesses which allows you to pick up some decent information about running processes I used outer applied sys.dm_exec_sql_text passing the sql_handle from sys.sysprocesses which gave me the text data for that given handle. I also used the statement start/end to get the exact point that the given process was up to (current_cmd), granted this was only really useful in multi statement batches, specifically stored procedures. You'll probably note that on a few occasions these SPID's won't have TextData available, I could've excluded these by using a cross apply but I'd personally I'd rather have a full scope of what's going on. I've thrown a basic order by in there but that's really up to you as to what order you wish to see the data, in later uses of this I found it handy to order it by any blocked processes so I could head straight to that SPID.
Enjoy, Rik.