To find all the queries running in all the sessions of SQL Server

Finding the sessions and queries running at the particular moment in SQL Server 

We do get into situations like where we have to find out who all are running what queries and what are its details. These type of questions do arise for DBA when ever he/she tries to work on performance issues.
I do get a situation in my work environment where one of my regularly scheduled was getting getting disturb by some unknown session.
I did scheduled the below the query to run at the same time for multiple times when my job invokes and captured the session details where problem was occuring.

Query to capture the session details at particular snapshot moment:

create table #temp_activity (spid smallint,dbid smallint,sql_handle binary(20),activity nvarchar(max)) 
GO 
insert into #temp_activity (Spid,dbid,sql_handle) 
(
select spid,dbid,sql_handle from master..sysprocesses where memusage !=0
) 
GO 
DECLARE @sqltext VARBINARY(128) 
DECLARE @num smallint 
DECLARE Get_activity CURSOR FOR 
Select spid from #temp_activity 
open Get_activity 
fetch next from Get_activity into @num 
while @@fetch_status=0 
Begin 
SELECT @sqltext = sql_handle FROM #temp_activity 
WHERE spid = @num 
update #temp_activity 
set activity=(select text FROM sys.dm_exec_sql_text(@sqltext)) where spid=@num 
fetch next from Get_activity into @num 
END 
Close Get_activity 
Deallocate Get_activity 
Go 
select * from #temp_activity 
===========================

One more query is given below which also gives the same information with some more details in it.

Use both the queries to find the session details with more details for troubleshooting purpose. =========================== 

select s.session_id,r.status , s.login_time , s.host_name , s.program_name,r.wait_time/1000 Wait_type_Sec ,

 s.cpu_time / 1000.0 as cpu_time , s.memory_usage*8 as memory_usage_Kb , s.total_scheduled_time / 1000.0 as total_scheduled_time , s.total_elapsed_time / 1000.0 as total_elapsed_time , s.last_request_end_time , s.reads, s.writes , s.login_name , s.nt_domain , s.nt_user_name , convert(char(100),c.connection_id) as connection_id , c.connect_time , c.num_reads , c.num_writes , c.last_read , c.last_write , c.client_net_address , c.client_tcp_port , c.session_id , convert(char(100),r.request_id) as request_id , r.start_time , r.command , r.open_transaction_count , r.open_resultset_count , r.percent_complete , r.estimated_completion_time , r.reads , r.writes , case when r.sql_handle is not null then (select top 1 SUBSTRING(t2.text, (r.statement_start_offset + 2) / 2, ( (case when r.statement_end_offset = -1 then ((len(convert(nvarchar(MAX),t2.text))) * 2) else r.statement_end_offset end) - r.statement_start_offset) / 2) from sys.dm_exec_sql_text(r.sql_handle) t2 ) else '' end as sql_statement FROM sys.dm_exec_sessions s left outer join sys.dm_exec_connections c on ( s.session_id = c.session_id ) left outer join sys.dm_exec_requests r on ( r.session_id = c.session_id and r.connection_id = c.connection_id ) WHERE s.is_user_process = 1 order by r.status desc 


==============================

Hope the above queries help in troubleshooting purpose

Thanks