sp_who3

USE [master]



GO







/****** Object: View [dbo].[vwWho3] Script Date: 07/27/2011 14:03:18 ******/


IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwWho3]'))



DROP VIEW [dbo].[vwWho3]



GO











CREATE VIEW vwWho3 



AS 







SELECT 



        dess.session_id AS [SPID],CASE der.status WHEN 'background' THEN 'BACKGROUND' ELSE der.status END AS 'Status' ,



        CONVERT(varchar(32),dess.original_login_name) AS [Login], 



        ISNULL(dess.host_name,'.') AS [HostName],CASE CONVERT(varchar(12),der.blocking_session_id) WHEN '0' THEN '.' 



        ELSE CONVERT(varchar(12),der.blocking_session_id) END AS BlkBy,



        DB_NAME(der.database_id) AS DBName,



        der.command AS 'Command',der.cpu_time AS [CPUTime],der.logical_reads AS [DiskIO],dess.last_request_start_time 



        AS [LastBatch],ISNULL(dess.program_name,'') AS [ProgramName],ISNULL(dest.text,'') AS 'CurrentQuery',



        ISNULL(deqp.query_plan,'') AS 'CurrentPlan',rgwg.name AS [ResourceWorkgroupName]



FROM sys.dm_exec_requests der 



INNER JOIN sys.resource_governor_workload_groups rgwg



            ON 



der.group_id = rgwg.group_id 



INNER JOIN sys.dm_exec_sessions dess 



            ON 



der.session_id = dess.session_id     



OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest



OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) deqp