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