How to find the deleted object detail in SQL server default trace
This SQL query helps identify deleted objects in SQL Server using T-SQL. It reads from the default trace and presents the relevant details.
;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'
