Jack Corbett (9/20/2010)

---

Here's a query that will find it:

With cteObjectTypes AS

(

SELECT

TSV.trace_event_id,

TSV.subclass_name,

TSV.subclass_value

FROM

sys.trace_subclass_values AS TSV JOIN

sys.trace_columns AS TC ON

TSV.trace_column_id = TC.trace_column_id

WHERE

TC.[name] = 'ObjectType'

),

cteEventSubClasses AS

(

SELECT

TSV.trace_event_id,

TSV.subclass_name,

TSV.subclass_value

FROM

sys.trace_subclass_values AS TSV JOIN

sys.trace_columns AS TC ON

TSV.trace_column_id = TC.trace_column_id

WHERE

TC.[name] = 'EventSubClass'

)

SELECT

TE.[name],

I.ApplicationName,

I.BigintData1,

I.ClientProcessID,

I.ColumnPermissions,

I.DatabaseID,

I.DatabaseName,

I.DBUserName,

I.Duration,

I.EndTime,

I.Error,

I.EventSequence,

Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,

I.FileName,

I.HostName,

I.IndexID,

I.IntegerData,

I.IsSystem,

I.LineNumber,

I.LoginName,

I.LoginSid,

I.NestLevel,

I.NTDomainName,

I.NTUserName,

I.ObjectID,

I.ObjectID2,

I.ObjectName,

Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,

I.OwnerName,

I.ParentName,

I.Permissions,

I.RequestID,

I.RoleName,

I.ServerName,

I.SessionLoginName,

I.Severity,

I.SPID,

I.StartTime,

I.State,

I.Success,

I.TargetLoginName,

I.TargetLoginSid,

I.TargetUserName,

I.TextData,

I.TransactionID,

I.Type,

I.XactSequence

FROM

sys.traces T CROSS Apply

sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0

THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'

ELSE T.[path]

End, T.max_files) I JOIN

sys.trace_events AS TE ON

I.EventClass = TE.trace_event_id LEFT JOIN

cteEventSubClasses AS ESC ON

TE.trace_event_id = ESC.trace_event_id And

I.EventSubClass = ESC.subclass_value LEFT JOIN

cteObjectTypes AS OT ON

TE.trace_event_id = OT.trace_event_id AND

I.ObjectType = OT.subclass_value

WHERE

T.is_default = 1 AND

TE.NAME = 'Object:Deleted'