May 3, 2016 at 6:18 am
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
May 3, 2016 at 6:59 am
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
May 3, 2016 at 7:32 am
Thanks for the reply one more thing, can you join sys.objects on your original script?
May 24, 2016 at 12:20 pm
hi Lowell,
Thanks for the script. Just need help to filter on the columns"
Only columns I wants are:
Date
Object Name
DatabaseName
Starttime
ServerName
User_name
ApplicationName
DDL operation
Can you please post updated script?
May 24, 2016 at 12:28 pm
here's the script with all columns in T.* exploded out to their full name.
you can remove what you don't need easier than i can.
object_name(object_id) has an additional optional parameter, db_id, so you cna add that to your list of columns
object_name(T.[ObjectID],T.[DatabaseID]) i think it goes.
--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,
object_name(T.[ObjectID],T.[DatabaseID]),
T.[TextData],
T.[BinaryData],
T.[DatabaseID],
T.[TransactionID],
T.[LineNumber],
T.[NTUserName],
T.[NTDomainName],
T.[HostName],
T.[ClientProcessID],
T.[ApplicationName],
T.[LoginName],
T.[SPID],
T.[Duration],
T.[StartTime],
T.[EndTime],
T.[Reads],
T.[Writes],
T.[CPU],
T.[Permissions],
T.[Severity],
T.[EventSubClass],
T.[ObjectID],
T.[Success],
T.[IndexID],
T.[IntegerData],
T.[ServerName],
T.[EventClass],
T.[ObjectType],
T.[NestLevel],
T.[State],
T.[Error],
T.[Mode],
T.[Handle],
T.[ObjectName],
T.[DatabaseName],
T.[FileName],
T.[OwnerName],
T.[RoleName],
T.[TargetUserName],
T.[DBUserName],
T.[LoginSid],
T.[TargetLoginName],
T.[TargetLoginSid],
T.[ColumnPermissions],
T.[LinkedServerName],
T.[ProviderName],
T.[MethodName],
T.[RowCounts],
T.[RequestID],
T.[XactSequence],
T.[EventSequence],
T.[BigintData1],
T.[BigintData2],
T.[GUID],
T.[IntegerData2],
T.[ObjectID2],
T.[Type],
T.[OwnerID],
T.[ParentName],
T.[IsSystem],
T.[Offset],
T.[SourceDatabaseID],
T.[SqlHandle],
T.[SessionLoginName],
T.[PlanHandle],
T.[GroupID]
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply