Listing 3: Script to Autostart a Trace Without Profiler
/****************************************************/
/* Created by: SQL Profiler                             */
/* Date: 02/13/2003  06:24:01 PM                  */
/****************************************************/
/* Comments added by SQL Mag */

-- Create a queue.
DECLARE @rc int
DECLARE @QueueHandle int
EXEC @rc = xp_trace_addnewqueue 1000, 5000, 95, 90, 69216261, @QueueHandle output if 
(@rc != 0) GOTO error

-- Set the events.
-- SQL Server 2000–specific events won't be scripted.

/* SQL MAG: 40 = SQL:StmtStarting - to get the SQL statement being executed */
   EXEC xp_trace_seteventclassrequired @QueueHandle, 40, 1

/* SQL MAG: 48 = Object Open - to check that the objects we're interested in are being opened */ 
   EXEC xp_trace_seteventclassrequired @QueueHandle, 48, 1

/* SQL MAG: 68 = Execution Plan - to see what indexes are being used in the plan */
   EXEC xp_trace_seteventclassrequired @QueueHandle, 68, 1


-- Set the filters.
/* SQL MAG - Ignore Profiler events. */
EXEC xp_trace_setappfilter @QueueHandle, N'', N'SQL Profiler'

/* Only look at DBID 6 (Northwind). */
EXEC xp_trace_setdbidfilter @QueueHandle, 6

/* Only look at the dbo.employees base object. */
EXEC xp_trace_setobjidfilter @QueueHandle, 117575457

/* Only look at SPID 7. */
EXEC xp_trace_setspidfilter @QueueHandle, 7

-- Set the destinations.

-- Save the definition in the registry.
EXEC xp_trace_savequeuedefinition  @QueueHandle,  N'Hunt for Index Usage', 1

-- Display queue handle for future references.
SELECT QueueHandle=@QueueHandle
GOTO finish

error:
SELECT ErrorCode=@rc

finish:
GO