Recently i have faced one scenario where one of our developers had changed one SP because of that application has been down no one able to pull out the reports and everyone denying that there is no changes made on database level in-spite of that application is not responding then I ran below query and found who had made changes in the database.
Below query provide you the all details about DDL changes made in database and server level:-
DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX(‘\’, REVERSE(path))+1) + ‘\Log.trc’
FROM sys.traces
WHERE is_default = 1;
SELECT gt.HostName,
gt.ApplicationName,
gt.NTUserName,
gt.NTDomainName,
gt.LoginName,
gt.SPID,
gt.EventClass,
te.Name AS EventName,
gt.EventSubClass,
gt.TEXTData,
gt.StartTime,
gt.EndTime,
gt.ObjectName,
gt.DatabaseName,
gt.FileName,
gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass in (164)
ORDER BY StartTime DESC;