RMJCS Logo
 

Text/HTML

 
 
if object_id('tempdb..#t') is not null drop table #t

select * into #t from master.dbo.sysprocesses

declare @justme bit = 0

select
	--sp.nt_domain,
	rtrim(sp.nt_username) as nt_username,
	sp.spid,
	--sp.kpid,
	rtrim(sp.status) as status,
	rtrim(sp.cmd) as cmd,
	case when sp.blocked = 0 then '' when sp.blocked = sp.spid then 'Self' else cast(sp.blocked as varchar(32)) end as blocked,
	case when sp.open_tran = 0 then '' else cast(sp.open_tran as varchar(32)) end as open_tran,
	sp.cpu,
	sp.physical_io,
	sp.memusage,
	--sp.waittype,
	sp.waittime,
	rtrim(sp.lastwaittype) as lastwaittype,
	rtrim(sp.waitresource) as waitresource,
	db_name(sp.dbid) as db_name,
	--sp.uid,
	sp.login_time,
	sp.last_batch,
	--sp.ecid,
	--sp.sid,
	rtrim(sp.hostname) as hostname,
	rtrim(sp.program_name) as program_name,
	--sp.hostprocess,
	--sp.net_address,
	--sp.net_library,
	--sp.loginame,
	--sp.context_info,
	--sp.sql_handle,
	--sp.stmt_start,
	--sp.stmt_end,
	--sp.request_id
   substring(st.text,r.statement_start_offset/2 ,(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), st.text)) * 2 else r.statement_end_offset end - r.statement_start_offset)/2 + 1) as statement
from 
	#t sp (nolock)
        left join sys.dm_exec_requests r  (nolock)on r.session_id = sp.spid
        outer apply sys.dm_exec_sql_text(r.sql_handle) st 
where 
    (sp.spid <> @@spid) and
   ((sp.spid > 50 and ((@justme = 0 or sp.dbid = db_id()) and sp.status <> 'sleeping')) or 
	(sp.spid > 50 and ((@justme = 0 or sp.dbid = db_id()) and sp.open_tran <> 0)) or 
	(sp.spid in (select distinct blocked from #t (nolock) where blocked <> 0)) or
	(sp.blocked <> 0)
   )
order by 
	case when sp.loginame = CURRENT_USER then 1 else 2 end, 
	sp.spid
 
     
 

RMJCS Syntax Highlighter

 
 
 
     
Copyright 2007-2015 RMJCS Ltd