--------------------------------------------------------------------------------
--  File        :   Demo 02_Summarize event types recorded by a trace,2005+.sql
--
--  Purpose     :   This script summarizes the types of events that were 
--                  recorded in the given trace file.
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   1.0004.0090.0000 (2010-02-02)
--------------------------------------------------------------------------------

USE [tempdb];

GO

SET NOCOUNT ON;

--  The path to the trace file whose events should be summarized.
DECLARE @TraceFilePath NVarChar(260);
:SetVar TraceFilePath E:\tnbarkhouse\Presentations\Presentation_SQLTrace\Scratch\Demo01.trc
--SetVar TraceFilePath E:\tnbarkhouse\Presentations\Presentation_SQLTrace\Demonstrations\Data\DB04_SQLDIAG__sp_trace.trc
--  The following value uses SQLCmd variable substitution.  Alternately, you 
--  can:
--      1.  Comment out line 28
--      2.  Uncomment line 29
--      3.  Replace the string value with the appropriate file path.
SET @TraceFilePath = N'$(TraceFilePath)';
--SET @TraceFilePath = N'C:\Temp\YourTraceFile.trc';

--  Summarize the events within the specified trace file.
SELECT [_Events].[EventClass],
    (
        SELECT [name]
        FROM [sys].[trace_events] AS [_EventDefinitions]
        WHERE ([_EventDefinitions].[trace_event_id] = [_Events].[EventClass])
    ) AS [EventClassName],
    Count([_Events].[EventClass]) AS [EventCount]
FROM [sys].[fn_trace_gettable](
    @TraceFilePath,
    DEFAULT
) AS [_Events]
GROUP BY [_Events].[EventClass]
ORDER BY [EventCount] DESC,
    [EventClassName] ASC;

GO
