[MSSQL] DMV - sp_who2 Version

아래 출처에 괜찮은 녀석이 있어.. 몇가지 추가해서 만들었습니다. ㅎㅎㅎ

출처 : http://www.extremeexperts.com/sql/Tips/sp_who2_Updated.aspx 

SELECT s.session_id AS spid

    ,s.[status]

    ,s.login_name AS loginName

    ,s.[host_name] AS hostName

    ,r.blocking_session_id AS blkBy

    ,r.wait_time

    ,r.wait_type

    ,r.last_wait_type  

    ,r.percent_complete

    ,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;