0

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

2

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;
  • I appreciate the query but I don't have the rights to run it. Msg 8189, Level 14, State 6, Line 1 You do not have permission to run 'SYS.TRACES'. – Newelle Horn Apr 15 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.