How to verify alerted or created or deleted objects on database

fn_trace_gettable returns trace file information in a tabular format. It helps to query the default or user-defined trace files.

fn_trace_gettable([@filename],[@numfiles])

@filename  -- which file to be read
@numfiles  -- Rollover files

To know the default system trace file.

SELECT * FROM fn_trace_getinfo(default);   -- Displays default trace file name

Below query returns the rows for created / altered / dropped objects from the current database

Select ObjectName, DatabaseName, StartTime,      
EventClass, ObjectType,ServerName, LoginName,      
NTUserName, ApplicationName
from ::fn_trace_gettable( 'C:\Program Files\Microsoft SQL Server\LOG\log_123.trc', default )           
where EventClass in (46,47,164) and EventSubclass = 0  and DatabaseID = db_id();

 Below query returns event class along with object_name

 Select ObjectName, DatabaseName, StartTime, te.name,EventClass, ObjectType,ServerName,   
 LoginName,NTUserName, ApplicationName
 from fn_trace_gettable( 'C:\Program Files\Microsoft \LOG\log_491.trc', default )           
 INNER JOIN sys.trace_events te  ON EventClass = te.trace_event_id
 where EventClass in (46,47,164) and EventSubclass = 0  and DatabaseID = db_id();

Below query returns about Stored procedure execution details from a particular application

Select ApplicationName, HostName, TextData, RowCounts, Reads, writes, CPU, Duration
from fn_trace_gettable( 'C:\program files\microsoft sql server\log\log_490.trc', default )           
where EventClass in (10,12)  -- Batch and RFC Completed
AND applicationname = '.Net SqlClient Data Provider'