Load all TRC Trace Files in a Folder into a Single Trace Table

This script will load all the trace (*.TRC) files in a given folder into a single trace table. You have to specify the database to create the trace table in, the folder the trace files are in, and the filename prefix (if any). Be careful: trace files can take up a lot of space in a hurry when imported into a table.

-- Load all TRC files in a given folder into a single trace table. 
set nocount on

-- Set these values. 
use MyMaintenanceDatabase
declare @TrcFolderName nvarchar(260)      set @TrcFolderName = 'C:\SQL_Trace\'
declare @TrcFilePrefix nvarchar(260)      set @TrcFilePrefix = 'MySpecificFilenamePrefixIfAny-'

-- Load the TRC filenames into temp table. 
declare @DosCmd nvarchar(4000)
set @DosCmd = 'dir /b ' + @TrcFolderName + @TrcFilePrefix + '*.trc'

if object_id('tempdb..#TrcFiles') is not null
    drop table #TrcFiles

create table #TrcFiles (TrcFileName nvarchar(260))

insert #TrcFiles
    exec master..xp_cmdshell @DosCmd 

delete #TrcFiles
 where TrcFileName is null

-- Iterate the trace files and read them into the trace table. 
declare @TrcFileName  nvarchar(260)
declare @TrcPathName  nvarchar(260)
declare @RowsAffected int

if object_id('dbo.trace_table') is not null
    drop table dbo.trace_table

declare cur cursor for
    select TrcFileName from #TrcFiles 
     
open cur
fetch next from cur into @TrcFileName

while @@fetch_status = 0
begin
    set @TrcPathName = @TrcFolderName + @TrcFileName
    raiserror('%s', 10, 1, @TrcPathName) with nowait

    if object_id('dbo.trace_table') is null
        select * into dbo.trace_table from ::fn_trace_gettable (@TrcPathName, /* number_files */ 1)
    else
        insert dbo.trace_table
            select * from  ::fn_trace_gettable (@TrcPathName, /* number_files */ 1)

    set @RowsAffected = @@rowcount
    raiserror('   %9d rows inserted', 10, 1, @RowsAffected) with nowait
    
    fetch next from cur into @TrcFileName
end

close cur
deallocate cur

-- Quirk in trace: update NULL DatabaseName's from their ID's. 
update dbo.trace_table
   set DatabaseName = db_name(DatabaseID)
 where isnull(DatabaseName, '')  = ''

set @RowsAffected = @@rowcount
raiserror('   %9d NULL DatabaseNames updated from their IDs', 10, 1, @RowsAffected) with nowait

-- Example cleanup: delete all rows that don't show their database. 
delete dbo.trace_table
 where DatabaseName is null

set @RowsAffected = @@rowcount
raiserror('   %9d NULL DatabaseNames deleted', 10, 1, @RowsAffected) with nowait

-- Example results: find unique logins per database per day. 
  select dateadd(dd, 0, datediff(dd, 0, StartTime))   as 'LoginDate'
       , LoginName
       , DatabaseName
    from dbo.trace_table
group by dateadd(dd, 0, datediff(dd, 0, StartTime))
       , LoginName
       , DatabaseName
order by LoginDate desc
       , LoginName
       , DatabaseName

Removing All Duplicate Tabs, Linefeeds, Returns, and Spaces

The trace tables created by SQL Profiler are nice and all, but it's a pain to search through the TextData column.  The code below uses a brute force method to replace all contiguous whitespace characters with a single space.  This makes it possible to search the TextData column without differences in whitespace getting in the way.

    -- Removing All Duplicate Tabs, Linefeeds, Returns, and Spaces. 

    DECLARE @RowsAffected INT = 0
    SET NOCOUNT ON
    
    -- Create all 16 (4^2) Tab, Linefeed, Return, and Space combinations.
    DECLARE @PairTT VARCHAR(2) = CHAR( 9) + CHAR( 9)    
    DECLARE @PairTL VARCHAR(2) = CHAR( 9) + CHAR(10)    
    DECLARE @PairTR VARCHAR(2) = CHAR( 9) + CHAR(13)    
    DECLARE @PairTS VARCHAR(2) = CHAR( 9) + CHAR(32)    
    DECLARE @PairLT VARCHAR(2) = CHAR(10) + CHAR( 9)    
    DECLARE @PairLL VARCHAR(2) = CHAR(10) + CHAR(10)    
    DECLARE @PairLR VARCHAR(2) = CHAR(10) + CHAR(13)    
    DECLARE @PairLS VARCHAR(2) = CHAR(10) + CHAR(32)    
    DECLARE @PairRT VARCHAR(2) = CHAR(13) + CHAR( 9)
    DECLARE @PairRL VARCHAR(2) = CHAR(13) + CHAR(10)
    DECLARE @PairRR VARCHAR(2) = CHAR(13) + CHAR(13)
    DECLARE @PairRS VARCHAR(2) = CHAR(13) + CHAR(32)
    DECLARE @PairST VARCHAR(2) = CHAR(32) + CHAR( 9)
    DECLARE @PairSL VARCHAR(2) = CHAR(32) + CHAR(10)
    DECLARE @PairSR VARCHAR(2) = CHAR(32) + CHAR(13)
    DECLARE @PairSS VARCHAR(2) = CHAR(32) + CHAR(32)
                                                    
    WHILE 1=1
    BEGIN
        SELECT @RowsAffected = COUNT(*) 
          FROM dbo.TraceTableReportPermanent
         WHERE TextData LIKE '%' + @PairTT + '%'
            OR TextData LIKE '%' + @PairTL + '%'
            OR TextData LIKE '%' + @PairTR + '%'
            OR TextData LIKE '%' + @PairTS + '%'
            OR TextData LIKE '%' + @PairLT + '%'
            OR TextData LIKE '%' + @PairLL + '%'
            OR TextData LIKE '%' + @PairLR + '%'
            OR TextData LIKE '%' + @PairLS + '%'
            OR TextData LIKE '%' + @PairRT + '%'
            OR TextData LIKE '%' + @PairRL + '%'
            OR TextData LIKE '%' + @PairRR + '%'
            OR TextData LIKE '%' + @PairRS + '%'
            OR TextData LIKE '%' + @PairST + '%'
            OR TextData LIKE '%' + @PairSL + '%'
            OR TextData LIKE '%' + @PairSR + '%'
            OR TextData LIKE '%' + @PairSS + '%'

        IF @RowsAffected > 0
            UPDATE dbo.TraceTableReportPermanent
               SET TextData = REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(TextData, @PairTT, CHAR(32)),
                                                @PairTL, CHAR(32)), 
                                                @PairTR, CHAR(32)), 
                                                @PairTS, CHAR(32)),
                                                @PairLT, CHAR(32)),
                                                @PairLL, CHAR(32)),
                                                @PairLR, CHAR(32)),
                                                @PairLS, CHAR(32)),
                                                @PairRT, CHAR(32)),
                                                @PairRL, CHAR(32)),
                                                @PairRR, CHAR(32)),
                                                @PairRS, CHAR(32)),
                                                @PairST, CHAR(32)),
                                                @PairSL, CHAR(32)),
                                                @PairSR, CHAR(32)),
                                                @PairSS, CHAR(32))
             WHERE TextData LIKE '%' + @PairTT + '%'
                OR TextData LIKE '%' + @PairTL + '%'
                OR TextData LIKE '%' + @PairTR + '%'
                OR TextData LIKE '%' + @PairTS + '%'
                OR TextData LIKE '%' + @PairLT + '%'
                OR TextData LIKE '%' + @PairLL + '%'
                OR TextData LIKE '%' + @PairLR + '%'
                OR TextData LIKE '%' + @PairLS + '%'
                OR TextData LIKE '%' + @PairRT + '%'
                OR TextData LIKE '%' + @PairRL + '%'
                OR TextData LIKE '%' + @PairRR + '%'
                OR TextData LIKE '%' + @PairRS + '%'
                OR TextData LIKE '%' + @PairST + '%'
                OR TextData LIKE '%' + @PairSL + '%'
                OR TextData LIKE '%' + @PairSR + '%'
                OR TextData LIKE '%' + @PairSS + '%'
        ELSE
            BREAK
             
        RAISERROR('Deleted %d whitespace pairs from dbo.TraceTable',
                   10, 1, @RowsAffected) WITH NOWAIT
    END