s.session_id,
db_name(s.database_id) DB,
s.login_time,
s.host_name,
s.program_name,
s.login_name,
s.cpu_time,
s.memory_usage,
s.total_scheduled_time,
s.total_elapsed_time,
s.endpoint_id,
s.last_request_start_time,
s.last_request_end_time,
s.reads,
s.writes,
s.logical_reads,
s.is_user_process,
s.row_count,
s.open_transaction_count,
t.text as [command]
from sys.dm_exec_sessions s
inner join sys.dm_exec_connections c on c.session_id = s.session_id
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) t
where s.session_id != @@spid
and status = 'running'
order by cpu_time desc
And for older versions of SQL Server, the the
following (Not Tested)
select
db_name(a.dbid) as [db_name],
hostname as [host_name],
NT_USERNAME,
spid,
physical_io,
cpu cpu2,
db_name(a.dbid) as [database_name],
program_name,
status,
text as [command]
From master..sysprocesses a
cross apply ::fn_get_sql(sql_handle) as command
where status = 'runnable'
--where
db_name(a.dbid) like 'temp%'
order by cpu desc