--------------------------------------------------------------------------------
--  File        :   Demo 04_Select object deletion events from the Default Trace,2005+.sql
--
--  Purpose     :   This script returns the events recorded by the default 
--                  trace for objects dropped from user databases on the 
--                  instance.
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   1.0005.0090.0000 (2010-02-02)
--------------------------------------------------------------------------------

USE [tempdb];

GO

SET NOCOUNT ON;

--  The path to the base Default Trace output file.  Each time that the trace 
--  rolls-over or is restarted, a new suffix is appended.  The 
--  [sys].[fn_trace_gettable] system function can target a specific trace file 
--  or a series.
DECLARE @BaseDefaultTraceFilePath NVarChar(260);

--  The path to the currently active output file of the Default Trace.
DECLARE @CurrentDefaultTraceFilePath NVarChar(260);

--  Look up the name of the currently active Default Trace file.
SELECT @CurrentDefaultTraceFilePath = [path]
FROM [sys].[traces]
WHERE ([path] LIKE N'%\log[_][0-9]%.trc');

--  Remove the suffix that indicates how many times the Default Trace has 
--  rolled-over/restarted.
SET @BaseDefaultTraceFilePath = SubString(
        @CurrentDefaultTraceFilePath,
        1,
        PatIndex(
            N'%\log[_][0-9]%.trc',
            @CurrentDefaultTraceFilePath
        )
    ) + N'log.trc';

--  Query specific information from the set of Default Trace files.
SELECT [_Events].[StartTime],
    (
        SELECT [name]
        FROM [sys].[trace_events] AS [_EventDefinitions]
        WHERE ([_EventDefinitions].[trace_event_id] = [_Events].[EventClass])
    ) AS [EventClassName],
    (
        SELECT [subclass_name]
        FROM [sys].[trace_subclass_values] AS [_EventSubClasses]
        WHERE (
            ([_EventSubClasses].[trace_event_id] = [_Events].[EventClass])
            AND
            ([_EventSubClasses].[trace_column_id] = 21)     --  EventSubClass
            AND
            ([_EventSubClasses].[subclass_value] = [_Events].[EventSubClass])
        )
    ) AS [EventSubClassName],
    [_Events].[DatabaseID],
    [_Events].[DatabaseName],
    [_Events].[ObjectID],
    [_Events].[ObjectName],
    (
        SELECT [subclass_name]
        FROM [sys].[trace_subclass_values] AS [_EventSubClasses]
        WHERE (
            ([_EventSubClasses].[trace_event_id] = [_Events].[EventClass])
            AND
            ([_EventSubClasses].[trace_column_id] = 28)     --  ObjectType
            AND
            ([_EventSubClasses].[subclass_value] = [_Events].[ObjectType])
        )
    ) AS [ObjectTypeName],
    [_Events].[SPID] AS [SessionID],
    [_Events].[LoginName],
    [_Events].[SessionLoginName],
    [_Events].[HostName],
    [_Events].[ApplicationName],
    [_Events].[ClientProcessID]
FROM [sys].[fn_trace_gettable](
    @BaseDefaultTraceFilePath,
    DEFAULT
) AS [_Events]
WHERE (
    ([_Events].[EventClass] = 47)                           --  Object:Deleted
    AND
    ([_Events].[DatabaseID] > 4)                            --  User databases
    AND
    ([_Events].[ObjectType] <> 21587)                       --  Statistics
--__TODO__
AND ([StartTime] >= '2011-04-20')
)
ORDER BY [_Events].[StartTime] ASC,
    [_Events].[ObjectName] ASC;

GO
