Does anyone know what the SQL Server trace Event ID or Event Name for Create Index is or where I could find the answer? I suspect it is 'Alter User Table' but I need to be certain.
+1
A:
In Profiler, what you want is in Objects -> Object:Created. This will show any DDL events. Then, in the column filters set the Like filter on the TextData column to Create Index%.
For a trace, you want the Object:Created event (eventId = 46). So something like:
sp_trace_setevent [your trace id]
, @eventid = 46 -- Object:Created
, @columnid = 1 -- TextData column
, [ @on = ] on
sp_trace_setfilter [your trace id]
, @columnid = 1 -- TextData column
, @logical_operator = 0 -- AND
, @comparison_operator = 6 -- LIKE
, @value = 'Create Index%'
Thomas
2010-04-18 19:34:16
yep, text filter is the only way AFAIK. The "Object:Created" event even mentions CREATE INDEX http://msdn.microsoft.com/en-us/library/ms186265.aspx I would use Create%Index% though because of NONCLUSTERED and UNIQUE usage, or add these as filters
gbn
2010-04-19 05:48:24
@gbn - RE: Filter. Excellent point on Create%Index%.
Thomas
2010-04-19 14:30:50