here is my script as stored procedure
CREATE PROCEDURE sp_mon_default_trace(@starttime datetime = null, @LoginName varchar(100) = null, @eventid int = null)
AS
SET NOCOUNT ON;
-- source http://www.sqlservercentral.com/scripts/SQL+Trace/65790/
-- details https://www.simple-talk.com/sql/database-administration/collecting-the-information-in-the-default-trace/?
DECLARE @tracefilevarchar(550)
DECLARE @rowidINT
DECLARE @dirVARCHAR(1000)
DECLARE @pathVARCHAR(1000)
DECLARE @sqlcmdVARCHAR(1000)
DECLARE @CMD VARCHAR(1024)
IF @starttime IS NULL
SET @starttime = convert(varchar(10), getdate(), 101)
CREATE TABLE #TableList (
DatabaseName SYSNAME,
TableName SYSNAME,
ObjectIdINT)
CREATE TABLE #MyTraceTable
(
TextData VARCHAR(8000),
HostNameVARCHAR(128),
LoginName VARCHAR(128),
ApplicationName VARCHAR(128),
DatabaseName VARCHAR(128),
ObjectNameVARCHAR(256),
CategoryNameVARCHAR(256),
EventNameVARCHAR(256),
SubEventNameVARCHAR(256),
TraceObjectName VARCHAR(128),
TraceObjectType VARCHAR(128),
ObjectID INT,
EventIDVARCHAR(128),
SubEventIDVARCHAR(128),
StartTime DATETIME,
)
DECLARE @tracefiles TABLE (rowid INT IDENTITY(1,1),FILE_NAME VARCHAR(1000))
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1 --select @path
SET @dir = ' dir "' + @path + '*.trc" /TW /OD /B' -- + @dir
-- this returns a collection of trace files
INSERT INTO @tracefiles
EXEC xp_cmdshell @dir -- this statement will dump all your .trc files into a memory table
SET @CMD = 'USE [?]; SELECT DB_NAME()DATABASE_NAME, NAME, OBJECT_ID FROM SYS.OBJECTS'
INSERT INTO #TableList
EXEC SP_MSFOREACHDB @CMD;
SELECT @rowid = MIN(rowid) FROM @tracefiles;
WHILE @rowid IS NOT NULL
BEGIN
SELECT @tracefile = @path + x.FILE_NAME FROM @tracefiles x WHERE rowid = @rowid
Insert into #MyTraceTable
select T.TextData,
T.HostName,
T.LoginName,
(CASE WHEN T.ApplicationName like 'SQLAgent - TSQL JobStep (Job %'
THEN j.name +' : '+(SUBSTRING(T.ApplicationName, CHARINDEX(':', T.ApplicationName) + 1, LEN(T.ApplicationName)-(CHARINDEX(':', T.ApplicationName)+1)))
ELSE T.ApplicationName END) as ApplicationName ,
T.DatabaseName,
L.TableName as ObjectName,
c.name as CategoryName,
E.name as EventClassName,
S.subclass_name as EventSubClassName,
T.ObjectName as TraceObjectName,
T.ObjectType as TraceObjectType,
T.ObjectID,
T.EventClass,
T.EventSubClass,
T.StartTime
FROM ::fn_trace_gettable(@tracefile,default) T
INNER JOIN sys.trace_events E with (nolock)
ON T.EventClass = E.trace_event_id
INNER JOIN sys.trace_categories C with (nolock)
ON C.category_id = e.category_id
LEFT OUTER JOIN sys.trace_subclass_values S with (nolock)
ON S.trace_event_id = T.EventClass and S.subclass_value = T.EventSubClass
LEFT OUTER JOIN #TableList L
ON L.OBJECTID = T.ObjectID and L.DatabaseName = T.DatabaseName
LEFT OUTER JOIN msdb.dbo.sysjobs J with (nolock)
ON (substring(left(j.job_id,8),7,2) +
substring(left(j.job_id,8),5,2) +
substring(left(j.job_id,8),3,2) +
substring(left(j.job_id,8),1,2)) = substring(T.ApplicationName,32,8)
WHERE T.SPID <> @@SPID;
SELECT @rowid = MIN(rowid) FROM @tracefiles WHERE FILE_NAME IS NOT NULL AND rowid > 0 AND rowid > @rowid
END
SELECT distinct * from #MyTraceTable
where StartTime > @starttime
and LoginName = ISNULL(@LoginName,LoginName)
and EventID = ISNULL(@EventID, EventID)
order by StartTime desc;
drop table #MyTraceTable;
drop table #TableList;