Who created that table in SQL Server?

Question

I was wondering if there was a way to see who created a table in SQL Server 2017.

Answer 1

The forensic information may still be available in the default trace if the table was created recently. The default trace writes to rollover files with a maximum of 5 files of 20MB each (100MB total) so the event may have rolled off, depending on system activity.

Below is an example T-SQL query. This is the same source used by the SSMS Schema Change History report (right-click on the server in SSMS Object Explorer and select Reports-->Standard Reports-->Schema Change History).

SELECT  
     trace.TextData
    ,trace.DatabaseName
    ,trace.ObjectName
    ,te.name AS EventName
    ,tsv.subclass_name
    ,trace.EventClass
    ,trace.EventSubClass
    ,trace.StartTime
    ,trace.EndTime
    ,trace.NTDomainName
    ,trace.NTUserName
    ,trace.LoginName
    ,trace.HostName
    ,trace.ApplicationName
    ,trace.Spid
    --,*
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
    FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
JOIN sys.trace_events AS te ON 
    trace.EventClass=te.trace_event_id
LEFT JOIN sys.trace_subclass_values AS tsv ON
    tsv.trace_event_id = EveNtClass
    AND tsv.subclass_value = trace.EventSubClass
WHERE
    te.name = 'Object:Created'
    AND trace.DatabaseName = N'YourDatabase'
    AND trace.ObjectName = N'YourTable'
    AND tsv.subclass_name = 'Commit'
ORDER BY trace.StartTime;