--------------------------------------------------------------------------------
--  File        :   Demo 08_Display the definition of the Default Trace,2005+.sql
--
--  Purpose     :   This script shows the event classes and columns that are 
--                  collected by the Default Trace.
--
--  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;

--  Determine the ID of the Default Trace by querying the [sys].[traces] 
--  catalog view.  Then pass that ID to the [sys].[fn_trace_geteventinfo] 
--  function function via the CROSS APPLY operator.  The output is then joined 
--  with several other catalog views, which provide lookup values for the 
--  event ID and column ID values.
SELECT [_EventClasses].[name] AS [EventClassName],
    (
        SELECT [name]
        FROM [sys].[trace_categories] AS [_EventCategories]
        WHERE ([_EventCategories].[category_id] = [_EventClasses].[category_id])
    ) AS [CategoryName],
    [_Columns].[name] AS [ColumnName],
    [_Columns].[type_name] AS [DataType],
    [_Columns].[max_size] AS [MaximumSize_bytes]
FROM [sys].[traces] AS [_ActiveTraces]
CROSS APPLY [sys].[fn_trace_geteventinfo](
    [_ActiveTraces].[id]
) AS [_Events]
LEFT OUTER JOIN [sys].[trace_events] AS [_EventClasses]
ON ([_EventClasses].[trace_event_id] = [_Events].[EventID])
LEFT OUTER JOIN [sys].[trace_columns] AS [_Columns]
ON ([_Events].[ColumnID] = [_Columns].[trace_column_id])
WHERE ([path] LIKE N'%\log[_][0-9]%.trc')
ORDER BY [CategoryName] ASC,
    [EventClassName] ASC,
    [ColumnName] ASC;

GO
