Default Trace Load of All 5 Files

--##################################################################################################
--Purpose: Load All The existing .trc files on a given server to a tracetable so they can be queried.
--Created By Lowell SQLServerCentral.com
--##################################################################################################
-- declare variables
DECLARE 
  @rowid          INT, 
  @dir            VARCHAR(1000), 
  @path           VARCHAR(1000), 
  @sqlcmd         VARCHAR(1000), 
  @filename       VARCHAR(1000),
  @TraceFileName  NVARCHAR(256)
DECLARE @trn TABLE (rowid INT IDENTITY(1,1),FILE_NAME VARCHAR(1000))

CREATE TABLE #MyTraceTable (
                            TraceFile       VARCHAR(128),
                            EventName       VARCHAR(128),
                            StartTime       DATETIME,
                            DatabaseID      INT,
                            DatabaseName    VARCHAR(128),
                            ObjectID        INT,
                            TraceObjectName VARCHAR(128),
                            SysobjectsName  VARCHAR(128),
                            type_desc       VARCHAR(128),
                            ObjectType      VARCHAR(128),
                            ObjectAbbrv     VARCHAR(128),
                            LoginName       VARCHAR(128),
                            HostName        VARCHAR(128),
                            ApplicationName VARCHAR(128) )
-- set variables
-- might return a long filename like c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\
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...5 in my case
INSERT INTO @trn 
  EXEC xp_cmdshell @dir  -- this statement will dump all your .trc files into a memory table

--  select * from @trn where file_name is not null
-- do stuff
SELECT @rowid = MIN(rowid) FROM @trn
WHILE @rowid IS NOT NULL
  BEGIN
-- do stuff
  SELECT @TraceFileName = @path + x.FILE_NAME FROM @trn  x WHERE rowid = @rowid
    INSERT INTO #MyTraceTable
      SELECT
      @TraceFileName,
      ev.name,
      tr.StartTime,
      tr.DatabaseID,
      tr.DatabaseName,
      tr.ObjectID,
      tr.ObjectName AS 'Trace ObjectName',
      o.name AS 'Sysobjects Name',
      o.type_desc,
      tr.ObjectType,
      sv.subclass_name AS 'ObjectAbbrv',
      tr.LoginName,
      tr.HostName,
      tr.ApplicationName
  FROM fn_trace_gettable(@TraceFileName, DEFAULT) tr
  INNER JOIN sys.trace_events ev
    ON  tr.eventclass = ev.trace_event_id
  INNER JOIN sys.trace_subclass_values sv
    ON  tr.eventclass = sv.trace_event_id
    AND tr.ObjectType = sv.subclass_value
    --and sv.trace_column_id = 28
  LEFT JOIN sys.objects o
    ON tr.ObjectID = o.OBJECT_ID

  SELECT @rowid = MIN(rowid) FROM @trn WHERE FILE_NAME IS NOT NULL AND rowID > 0 AND rowid > @rowid
END


SELECT * FROM #MyTraceTable ORDER BY starttime
--drop table #MyTraceTable