how to trace SQL table drop create events

Question

I have some mysterious problem where every day one table in DB (SQL Server 2016) is being recreated (I suppose dropped and created) with old data. I checked various options to try to find what process is doing this, however was unable to do that.

Scheduled Tasks - nothing
SQL Agent Jobs - nothing

How to trace what user/application/anythingelse is doing this ?

I tried launching SQL Profiler and starting manual trace, but after some time (half a day or so) it just stopped.

Answer 1

The default trace captures schema changes. Review the Schema Change History report or run the query below to retrieve the info in T-SQL. Note that the default trace rollover files are limited to 5 files of up to 20MB each so older events may have rolled off.

--select object created and deleted information from default trace
SELECT 
      trace_table.StartTime
    , te.name
    , trace_table.ObjectName
    , trace_table.ApplicationName
    , trace_table.LoginName
FROM (
    SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)) , 255)) + 'log.trc'
    FROM sys.traces
    WHERE
        is_default = 1
    ) AS trace(path)
CROSS APPLY sys.fn_trace_gettable(trace.path, DEFAULT) AS trace_table
JOIN sys.trace_events AS te ON
    te.trace_event_id = trace_table.EventClass
WHERE 
    EventSubClass = 0
    AND name IN('Object:Created', 'Object:Deleted')
ORDER BY StartTime;