DMV's sp_who2 Script

I have had a chance to talk on the new DMV's from SQL Server at various forums. And from time to time I have been a strong advocate that the DMV's actually expose more information than what we used to get in SQL 2000 from DBCC or other commands. Moreover, DMV's are quite open in their values rather than the conventional bit manipulation we were used to. Here is a classic example of DMV's where we show the query used to mimic sp_who2 output for user connections.

This query also exposes information like the query last executed by the connection and the Isolation level the connection is currently executing.

SELECT s.session_id AS spid
    ,s.[status]
    ,s.login_name AS loginName
    ,s.[host_name] AS hostName
    ,NULL AS blkBy
    ,DB_NAME(r.database_id) AS dbName
    ,r.command
    ,s.cpu_time AS cpuTime
    ,s.reads + s.writes AS diskIO
    ,s.last_request_end_time AS lastBatch
    ,s.[program_name] AS programName
    ,s.session_id
    ,r.request_id
    ,CASE
        WHEN s.transaction_isolation_level = 0 THEN 'Unspecified'
        WHEN s.transaction_isolation_level = 1 THEN 'ReadUncommitted'
        WHEN s.transaction_isolation_level = 2 THEN 'ReadCommitted'
        WHEN s.transaction_isolation_level = 3 THEN 'Repeatable'
        WHEN s.transaction_isolation_level = 4 THEN 'Serializable'
        WHEN s.transaction_isolation_level = 5 THEN 'Snapshot'
    END AS transactionIsolationLevel
    ,OBJECT_NAME(t.objectid) AS objectName
    ,t.[text] AS lastSQLText
FROM sys.dm_exec_sessions AS s
    LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
    LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE s.is_user_process = 1;

A typical output is shown below: