This query works in 2005 and above versions of sql server. Information regrading queries running at particular moment is captured using DMV's.
This script will show the currently running queries in the system. It will give the entire script which is involved in the background.
Also if the query is running for a long time, it will show what actually is causing slowness. Eg. Network delay,open tran etc...
select a.session_id,blocking_session_id,db_name(a.database_id) as database_name, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text, qs.total_worker_time as CPU_Time, qs.total_elapsed_time, qs.total_worker_time/qs.execution_count as avg_cpu_time, qs.total_elapsed_time/qs.execution_count as avg_elapsed_time, last_worker_time as last_cpu_time, qs.last_elapsed_time, qs.execution_count, qs.creation_time, qs.last_execution_time, qs.total_physical_reads, qs.last_physical_reads, a.row_count as row_count_returned, wait_type, wait_resource, open_transaction_count from sys.dm_exec_query_stats qs join sys.dm_exec_requests a on qs.sql_handle = a.sql_handle cross apply sys.dm_exec_sql_text(qs.plan_handle) as st group by st.text, qs.statement_start_offset, qs.statement_end_offset, qs.total_worker_time, qs.total_elapsed_time, qs.last_elapsed_time, a.session_id, a.database_id, qs.last_worker_time, qs.execution_count, qs.creation_time, qs.last_execution_time, qs.total_physical_reads, qs.last_physical_reads, a.row_count, blocking_session_id, wait_type, wait_resource, open_transaction_count
