We can be find out who made DDL Changes in SQL Server database through the sys.traces dynamic management view . sys.traces DMV’s provides you details about below points:-

who dropped tables in a database

who altered tables in database

who created table in database

who created database

who altered or changes the stored procedure

who dropped or altered the schema

Firstly we are going to find the list of Trace event details in SQL Server and with the help of event id proceed further:-

SELECT DISTINCT e.trace_event_id , e.name FROM sys.fn_trace_geteventinfo (1) fgt JOIN sys.trace_events e ON fgt.eventID = e.trace_event_id

Above output shown different-different events like Object:Altered, Object:Created and Object:Deleted.

With the help of event id we can find out who had dropped, altered or created database object in SQL Server.

I have used event class id 46,47 and 164 to track database Object:Altered, Object:Created and Object:Deleted

Find the below query to track who dropped or created or altered database object in SQL Server :-

DECLARE @File_Name NVARCHAR(250) SELECT @File_Name = SUBSTRING(path, 0,LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'FROM sys.traces WHERE is_default = 1 ; SELECT loginname ,hostname ,applicationname , databasename ,objectName ,starttime ,e.name AS EventName ,databaseid FROM sys.fn_trace_gettable(@File_Name, DEFAULT) AS tgt INNER JOIN sys.trace_events e ON tgt.EventClass = e.trace_event_id WHERE ( tgt.EventClass = 47 OR tgt.EventClass = 164 or tgt.EventClass=46) AND tgt.EventSubClass = 0 order by StartTime desc

After execution of above query you will get the output like below:-