SELECT
	CASE WHEN 
	CAST( LTRIM(RTRIM(s.blocked)) AS SMALLINT) > 0 
	THEN 'Waiting / Blocked' 
	ELSE 'Blocking' End 		AS 'Process_Type',
	LTRIM(RTRIM(s.spid))		AS 'SPID',
	LTRIM(RTRIM(s.blocked))		AS 'Blocked',
	LTRIM(RTRIM(s.cpu))		AS 'CPU', 
	db_name(LTRIM(RTRIM(s.dbid)))	AS 'DBName',
	LTRIM(RTRIM(s.login_time))	AS 'Login_Time', 
	LTRIM(RTRIM(s.last_batch))	AS 'Last_Batch',
	LTRIM(RTRIM(s.status))		AS 'Status',
	LTRIM(RTRIM(s.loginame))	AS 'LoginName', 
	LTRIM(RTRIM(s.hostname))	AS 'HostName', 
	LTRIM(RTRIM(s.program_name))	AS 'ProgramName',
	LTRIM(RTRIM(s.cmd))		AS 'CMD',
	LTRIM(RTRIM(EST.TEXT))		AS 'Full_Query',
	LTRIM(RTRIM(s.waittime))	AS 'Wait_Time',
	LTRIM(RTRIM(s.lastwaittype))	AS 'Wait_Type',
	LTRIM(RTRIM(s.waitresource))	AS 'Wait_Resource',
	LTRIM(RTRIM(s.cpu))		AS 'CPU_Time(MS)',
	LTRIM(RTRIM(s.physical_io))	AS 'Disk R/W',
	LTRIM(RTRIM(s.memusage))	AS 'Mem_Usage(Total_Pages)',
	LTRIM(RTRIM(s.open_tran))	AS 'NoOfOpenTran',
	LTRIM(RTRIM(s.nt_domain))	AS 'Windows_Domain',
	LTRIM(RTRIM(s.nt_username))	AS 'Windows_UserName'
FROM	sys.sysprocesses s
	CROSS APPLY sys.dm_exec_sql_text(s.sql_handle)EST
WHERE	spid in(select spid from sys.sysprocesses where blocked<>0) or
	spid in(select blocked from sys.sysprocesses); 





SELECT 
	CASE WHEN 
	CAST( LTRIM(RTRIM(s.blocked)) AS SMALLINT) > 0 
	THEN 'Waiting / Blocked' 
	ELSE 'Blocking' End 		AS 'Process_Type',
	LTRIM(RTRIM(s.spid))		AS 'SPID',
	LTRIM(RTRIM(s.blocked))		AS 'Blocked',
	db_name(LTRIM(RTRIM(s.dbid)))	AS 'DBName',
	LTRIM(RTRIM(s.login_time))	AS 'Login_Time', 
	LTRIM(RTRIM(s.last_batch))	AS 'Last_Batch',
	LTRIM(RTRIM(s.status))		AS 'Status',
	LTRIM(RTRIM(s.loginame))	AS 'LoginName', 
	LTRIM(RTRIM(s.hostname))	AS 'HostName', 
	LTRIM(RTRIM(s.program_name))	AS 'ProgramName',
	LTRIM(RTRIM(s.cmd))		AS 'CMD',
	ER.[statement_start_offset]	AS 'Statement_Start',
	ER.[statement_end_offset]	AS 'Statement_End',
	EST.TEXT			AS 'Full_Query',	
	CASE
	 WHEN ER.[statement_start_offset] > 0 THEN
		CASE ER.[statement_end_offset]  
		   WHEN -1 THEN  
			  SUBSTRING(EST.TEXT, (ER.[statement_start_offset]/2) + 1, 2147483647) 
		   ELSE   
			  SUBSTRING(EST.TEXT, (ER.[statement_start_offset]/2) + 1, (ER.[statement_end_offset] - ER.[statement_start_offset])/2)   
		END  
	 ELSE  
		CASE ER.[statement_end_offset]  
		   WHEN -1 THEN  
			  RTRIM(LTRIM(EST.[text]))  
		   ELSE  
			  LEFT(EST.TEXT, (ER.[statement_end_offset]/2) +1)  
		END  
	END				AS 'Exact_Statement',
	LTRIM(RTRIM(s.waittime))	AS 'Wait_Time',
	LTRIM(RTRIM(s.lastwaittype))	AS 'Wait_Type',
	LTRIM(RTRIM(s.waitresource))	AS 'Wait_Resource',
	LTRIM(RTRIM(s.cpu))		AS 'CPU_Time(MS)',
	LTRIM(RTRIM(s.physical_io))	AS 'Disk R/W',
	LTRIM(RTRIM(s.memusage))	AS 'Mem_Usage(Total_Pages)',
	LTRIM(RTRIM(s.open_tran))	AS 'NoOfOpenTran',
	LTRIM(RTRIM(s.nt_domain))	AS 'Windows_Domain',
	LTRIM(RTRIM(s.nt_username))	AS 'Windows_UserName'
FROM	sys.dm_exec_requests ER 
	CROSS APPLY sys.dm_exec_sql_text(ER.[sql_handle]) EST  
	INNER JOIN sys.sysprocesses s ON ER.session_id = s.spid
WHERE	ER.session_id IN
	(SELECT spid FROM sys.sysprocesses WHERE blocked<>0
	UNION
	SELECT blocked FROM sys.sysprocesses) 
ORDER BY	ER.[session_id], ER.[request_id];