USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_sp_who4]    Script Date: 06/26/2008 11:31:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_sp_who4] 
(@filterspid int = NULL, @filter tinyint = 1)
RETURNS @processes TABLE
(
  spid int,    
  blocked int,    
  databasename varchar(256),    
  hostname varchar(256),    
  current_statement_parent xml,
  current_statement_sub xml,
  program_name varchar(256),    
  loginame varchar(256),    
  status varchar(60),    
  cmd varchar(128),    
  cpu int,    
  physical_io int,    
  [memusage] int,    
  login_time datetime,    
  last_batch datetime  
)
AS
BEGIN

INSERT INTO @processes    
SELECT sub.*
FROM
(	SELECT sp.spid,    
		  sp.blocked,    
		  sd.name,    
		  RTRIM(sp.hostname) AS hostname, 
		  ( SELECT LTRIM(st.text) AS [text()]
			FOR XML PATH(''), TYPE) AS parent_text,  
		  ( SELECT 
			LTRIM(CASE
			WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL
			ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1, 
							((CASE er.statement_end_offset
									WHEN -1 THEN DATALENGTH(st.text)
									ELSE er.statement_end_offset
									END - er.statement_start_offset)/2) + 1)  
			END) AS [text()]
			FROM sys.dm_exec_requests er 
			CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
			WHERE er.session_id = sp.spid
			FOR XML PATH(''), TYPE) AS child_text,
		  RTRIM(sp.[program_name]) AS [program_name],    
		  RTRIM(sp.loginame) AS loginame,    
		  RTRIM(sp.status) AS status,    
		  sp.cmd,    
		  sp.cpu,    
		  sp.physical_io,    
		  sp.memusage,    
		  sp.login_time,    
		  sp.last_batch	  
	FROM  sys.sysprocesses sp (NOLOCK) 
	LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid
	CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
) sub 
INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid
ORDER BY sub.spid

-- if specific spid required    
IF @filterspid IS NOT NULL    
 DELETE @processes    
 WHERE spid <> @filterspid    
    
-- remove system processes    
IF @filter = 1 OR @filter = 2    
 DELETE @processes    
 WHERE spid < 51 OR spid = @@SPID 
    
-- remove inactive processes    
IF @filter = 2    
 DELETE  @processes    
 WHERE status = 'sleeping'    
   AND cmd IN ('AWAITING COMMAND')    
   AND blocked = 0 
RETURN
END