1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104

use master
go

if object_id('sp_now') is not null
	drop procedure sp_now
go

create procedure [dbo].[sp_now]
@session_id int = null
,@xml_on bit = 0
,@text nvarchar(500) =''
,@back_on bit = 0
as
/***********************************************************************************************
**Object Name: dbo.sp_now
**
**Description: Revise sp_who2
**
**Input Parameters: 
	@session_id int = null
	, @text nvarchar(500) ='
	, @back_on bit = 0
**
**Return Value: N/A
**
**Return Result Set: session info
**
**Creator: Simon Cho
**
**Create Date: 09/13/2011
**
**Example: exec [sp_now]
**
**Change History
**
**Change Date			Changed By				Reason
**9/24/2012				Simon Cho				query_plan off, remove text field.
**9/25/2012				Simon Cho				Change default value @xml_on=0
*************************************************************************************************/
begin
set nocount on;
set transaction isolation level read uncommitted;

select
	   sp.spid as s_id
	 , max(sp.blocked) as BlkBy
	 , substring(max(convert(varchar(23),r.start_time,121)+sp.status), 24, 50) status
	 , rtrim(max(sp.loginame)) as Login
	 , rtrim(ltrim(max(sp.hostname))) as hostname
	 , min(r.start_time) as st_time
	 , convert(numeric(10,2),max(r.total_elapsed_time/1000)/60.0) [run_time(min)]
	 --, r.total_elapsed_time
	 , GETDATE() as current_datetime
	 , max(sp.waittime) as waittime
	 , substring(max(convert(varchar(23),r.start_time,121)+r.wait_type), 24, 50) wait_type
	 , db_name(max(sp.dbid)) as db
	 , substring(max(convert(varchar(23),r.start_time,121)+command), 24, 50) command
	 , max(r.cpu_time) as cpu_time
	 , max(r.reads) as reads
	 , max(r.writes) as writes
	 , max(r.logical_reads) as logical_reads
	 , rtrim(max(sp.program_name)) as program_name
	 , max(c.client_net_address) ip
	 , min(sp.login_time) as login_time
	 , convert(numeric(10,2),max(r.granted_query_memory)/128.0) as granted_query_memory_MB
	 --, t.objectid
	 , OBJECT_NAME(t.objectid, t.dbid) objname
	 , convert(xml,'<?Q--'+CHAR(13)+CHAR(10)+substring(max(case when text like 'Fetch%' then substring(text, 1,32) else text end)
				, (case when (statement_start_offset/2)+1<len(max(case when text like 'Fetch%' then substring(text, 1,32) else text end)) then (statement_start_offset/2)+1 else 1 end)
				, (case statement_end_offset when -1 then datalength(max(case when text like 'Fetch%' then substring(text, 1,32) else text end)) else (statement_end_offset- statement_start_offset)/2 +1 end )
				) +CHAR(13)+CHAR(10)+'--?>') as text_stmt_xml

	 --, max(t.text) as text
	 , case when @xml_on = 1 then (select query_plan from sys.dm_exec_query_plan(max(r.plan_handle)))
							 else convert(xml, '')
	   end as query_plan
  from sys.sysprocesses sp with(nolock)
  join sys.dm_exec_requests r with(nolock) on r.session_id=sp.spid
  left join sys.dm_exec_connections c with(nolock) on r.connection_id=c.connection_id
 outer apply sys.dm_exec_sql_text(r.sql_handle) t
 where 1=1
-- and sp.spid!=@@SPID
   and case when @session_id is null then 1 else sp.spid end
     = case when @session_id is null then 1 else @session_id end
   and case when @text='' then '1' else text end
  like case when @text='' then '1' else '%'+@text+'%' end
   and case when @back_on = 0 then sp.spid else 51 end > 50
   and case when @back_on = 0 then sp.status else 'all' end != 'background'
 group by sp.spid
	 , sp.dbid, t.dbid
	 , t.objectid
	 , r.statement_start_offset
	 , r.statement_end_offset
	 , r.transaction_id
	 , c.connection_id
	 , sp.blocked

end
go

--exec sys.sp_MS_marksystemobject 'sp_now'

exec sp_now