Tac11 (5/3/2016)
Hi Lowell,in the result header there are 2 columns 'date' and 'Start_time' what are these can you put specific column name? plus if I want to filter by date like 'modified Store proc date' since 01-01-2016.
Thanks
the columns in the default trace are when the command was executed, and does not have the modified_date of the proc;that is store din the sys.objects table anyway.
even worse, the default trace rolls over with lots of activity; one indexing event, or one problem app that is getting login failed can erase all the history about objects that really changed. a busy system might only have a few hours of results in the trace.
instead, you need to create your own trace, or even better, an extended event that is capturing the DDL changes, so you don't suffer from the truncation/rollover problem.
a query like this would show you what is in the default trace now:
--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)
SET @TraceIDToReview = 1 --this is the trace you want to review! 1=Default Trace
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
WHERE TE.name IN('Object:Created','Object:Altered')
and T.DatabaseName NOT IN('tempdb','ReportServer','ReportServerTempDB')
AND T.StartTime >= DATEADD(dd, DATEDIFF(dd,0,getdate()), -3) --last three days?
Lowell