USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_helptrace]    Script Date: 16/09/2022 15:06:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[sp_helptrace] 
 @event varchar(255) = null  /*event to show*/
 ,@top varchar(10) = null    /*top NUM*/
 ,@file varchar(512) = null  /*specific trace file*/
 ,@db nvarchar(128) = null   /*info per database*/
 ,@user varchar(128) = null
 ,@exec tinyint = 1
as 
begin
	set nocount on

	declare @cmd varchar(2048)
	declare @all_files varchar(512)
	declare @traces table (id int,filepath nvarchar(260),[status] varchar(20))
	declare @id int
	declare @2print nvarchar(300)
	
	if isnull(@top,'')='' 
		select @top='100'

	select @user=isnull('and ft.LoginName like ''%'+@user+'%'' ','')

	/* prepare path for all files */
	select @all_files=CAST(value AS VARCHAR(200))
	FROM sys.fn_trace_getinfo(DEFAULT)
	WHERE traceid = 1 AND property = 2
	select @all_files=left(@all_files,len(@all_files)-charindex('_',reverse(@all_files)))+right(@all_files,4)

	
	/*informational*/
	select @cmd='Default trace current file: ['+ path+']' from sys.traces where is_default=1
	print @cmd
	
	select @cmd='Start time: '+convert(varchar(20),start_time,106) + ' '+convert(varchar(20),start_time,108) 
	from sys.traces where is_default=1
	print @cmd
	
	select @cmd='Last event: '+convert(varchar(20),last_event_time,106) + ' '+convert(varchar(20),last_event_time,108) 
	from sys.traces where is_default=1
	print @cmd
	
	if exists (select top 1 1 from sys.traces where is_default<>1)
		begin
			insert into @traces (id,filepath,[status])
			select id
				,[path]
				,case 
					when status=0 then 'Stopped'
					when status=1 then 'Running'
					else convert(varchar(20),[status])
					end
			from sys.traces
			where is_default<>1

			print ''
			print 'Other traces:'
			select @2print=''

			while exists (select top 1 1 from @traces)
				begin
					select top 1 @id=id
						,@2print= convert(nvarchar(10),id)+' : '+filepath+' ['+[status]+']'
					from @traces 
					order by id

					print @2print
					
					delete @traces where id=@id
					select @2print=''
				end
		end


	if not exists (select 1 from sys.traces where is_default=1 and status=1 )
		begin
			print 'No active default trace running!'
			select @cmd='
						/* *** How to enable *** */
						/*check*/
						select * from master.sys.traces where is_default=1
						go

						/*enable*/
						exec sp_configure ''show advanced'',1 
						reconfigure 
						go
						exec sp_configure ''default trace enabled'',0
						reconfigure 
						go
						exec sp_configure ''default trace enabled'',1
						reconfigure 
						go

						/*check*/
						select * from master.sys.traces where is_default=1
						go
						'
			print @cmd

			return
		end
	/* -- end -- */
	
	select @cmd=''
	
	/**/
	if (@event is null) --and (@file is null)
		/*check ALL trace files and show all events and their frequency*/
		select @cmd='
					use master;
					select
						tc.name [EventCategory] 
						, te.name [EventDesc]
						, ft.EventClass [ClassID]
						, count(1) [Events]
						, sum(case when ft.StartTime >=convert(date,getdate()) then 1 else 0 end) [HappenedToday]
						, min(ft.StartTime) [FirstEvent]
						, max(ft.StartTime) [LastEvent]
						, case
							when datediff(ss,min(ft.StartTime),max(ft.StartTime))>86400  then convert(varchar(10),datediff(dd,min(ft.StartTime),max(ft.StartTime)))+'' day(s) ''
							else '''' end 
							+ convert(varchar(10),dateadd(ss,datediff(ss,min(ft.StartTime),max(ft.StartTime)),0),108) [DepthOfData]
						
						,case
							when datediff(ss,max(ft.StartTime),getdate())>86400  then convert(varchar(10),datediff(dd,max(ft.StartTime),getdate()))+'' day(s) ''
							else '''' end 
							+ convert(varchar(10),dateadd(ss,datediff(ss,max(ft.StartTime),getdate()),0),108) [Ago]
						
					FROM sys.traces t CROSS Apply
						::fn_trace_gettable('''+ ISNULL(@file,@all_files) +''', '+case when @file is null then 't.max_files' else 'null' end+') AS ft 
					INNER JOIN sys.trace_events AS te ON ft.EventClass = te.trace_event_id 
					INNER JOIN sys.trace_categories tc on te.category_id=tc.category_id
					where 
						t.is_default=1 
						and t.status=1
						'+@user+'
					group by tc.name, te.name, ft.EventClass
					order by 1,2;
					'

	if (@event is not null) --and (@file is null)
	/*check ALL trace files and show event + additional info*/
		select @cmd='
					use master;
					SELECT	top '+@top+'
							te.name, 
							ft.SPID,
							ft.LoginName,
							ft.Hostname,
							ft.ClientProcessID,
							ft.DatabaseName, 
							ft.FileName, 
							ft.StartTime, 
							dateadd(ms,ft.Duration/1000,ft.StartTime) [FinishTime],
							ft.ApplicationName ,
							ft.ObjectID,
							ft.Duration/1000/1000 [Duration (s)],
						ft.TextData,
						ft.RoleName,
						ft.TargetUserName,
						ft.TargetLoginName
					FROM sys.traces st CROSS Apply
						::fn_trace_gettable('''+ ISNULL(@file,@all_files) +''', '+case when @file is null then 'st.max_files' else 'NULL' end+') AS ft 
					INNER JOIN sys.trace_events AS te ON ft.EventClass = te.trace_event_id 
					WHERE 
						te.name LIKE '''+@event+'''  
					'+@user+' 
					and st.is_default=1 
					and st.status=1
					ORDER BY ft.StartTime desc;
					'

	if (@db is not null) 
		begin
			select @event=isnull('and te.name LIKE '''+@event+'''','')
			--select @file=ISNULL(@file,@all_files)

			if @db=''
				begin
					select @cmd='
					SELECT
						ft.DatabaseName
						, tc.name
						, te.name
						, count(te.name) [Events]
					FROM sys.traces st 
						CROSS Apply ::fn_trace_gettable('''+ISNULL(@file,@all_files)+''', '+case when @file is null then 'st.max_files' else 'null' end +') AS ft 
						INNER JOIN sys.trace_events AS te ON ft.EventClass = te.trace_event_id 
						INNER JOIN sys.trace_categories tc on te.category_id=tc.category_id
					WHERE 
						st.is_default=1 
						and st.status=1
				group by ft.DatabaseName, tc.name, te.name
				order by 1,2,3,4 desc'

				end
			else
				select @cmd='
							use master;
							SELECT	top '+@top+'
									te.name, 
									ft.SPID,
									ft.LoginName,
									ft.Hostname,
									ft.ClientProcessID,
									ft.DatabaseName, 
									ft.FileName, 
									ft.StartTime, 
									dateadd(ms,ft.Duration/1000,ft.StartTime) [FinishTime],
									ft.ApplicationName ,
									ft.ObjectID,
									ft.Duration/1000/1000 [Duration (s)],
								ft.TextData,
								ft.RoleName,
								ft.TargetUserName,
								ft.TargetLoginName

							FROM sys.traces st CROSS Apply
								::fn_trace_gettable('''+ ISNULL(@file,@all_files) +''', '+case when @file is null then 'st.max_files' else 'null' end+') AS ft 
							INNER JOIN sys.trace_events AS te ON ft.EventClass = te.trace_event_id 
							WHERE 1=1 
							'+@event+'
							'+@user+'
							and ft.DatabaseName='''+@db+''' 
							and st.is_default=1 and st.status=1
							ORDER BY ft.StartTime desc;
							'
			
		end
		
	print '  '
	print '/* --------  Current Query  --------- */'
	print @cmd
	if @exec=1
		exec (@cmd)
	  
end