If your environment doesn’t have any kind of auditing and you need to find since when an object was dropped.
If you have the default trace enabled (good practice) you can track when the object was dropped and determine if that was the root cause for some performance issue for example.
The point here is to find the root cause of an issue, not point the finger to who did it 😉
First, let’s check what kind of events the default trace has
SELECT DISTINCT Trace.EventID , TraceEvents.NAME AS Event_Desc FROM ::fn_trace_geteventinfo(1) Trace JOIN sys.trace_events TraceEvents ON Trace.eventID = TraceEvents.trace_event_id
You are going to see the events
The next step is get the default trace path
SELECT path FROM sys.traces WHERE is_default = 1;
Knowing the path you can get all the files name using xp_cmdshell for example
EXEC xp_cmdshell 'dir e:\"Program Files"\"Microsoft SQL Server"\"MSSQL10.MSSQLSERVER"\MSSQL\*.trc'
Grab the file name you want and change the script below
SELECT CASE EventClass WHEN 46 THEN 'Object:Created' WHEN 47 THEN 'Object:Deleted' WHEN 164 THEN 'Object:Altered' END , DatabaseName , ObjectName , HostName , ApplicationName , LoginName , StartTime FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\your_file_name.trc', DEFAULT) WHERE EventClass IN (46,47,164) AND EventSubclass = 0 ORDER BY StartTime DESC
In my scenario what is showing is the creating object in the bottom with some index creation, drop index, object alter (it’s not telling what kind of change), create a new object and alter (the primary key creates the index and alter the table adding the constraint).
Plus you can see the login name and when the action was made. 😉