-- *********************************************************************************
-- ***  Crear/Arrancar Traza de Creación, Modificación o Eliminación de Objetos  ***
-- *********************************************************************************
--
-- GuilleSQL - Un portal sobre Microsoft SQL Server en Castellano
-- https://guillesql.es
---- ***********************************************************************
-- *** Si está la Traza en ejecución => Detener la traza y eliminarla  ***
-- ***********************************************************************
IF EXISTS(select traceid from fn_trace_getinfo(0) where cast(value as varchar(1000)) like '%\objects_created_altered_deleted_%')
BEGIN
   declare @tmp_traceid int
   select @tmp_traceid = traceid from fn_trace_getinfo(0) where cast(value as varchar(1000)) like '%\objects_created_altered_deleted_%'
   exec sp_trace_setstatus @tmp_traceid, 0
   exec sp_trace_setstatus @tmp_traceid, 2
END


-- ************************
-- ***  Crear la Traza  ***
-- ************************
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50 
declare @trace_file nvarchar(245)
set @trace_file = 'g:\sqltraces\objects_created_altered_deleted_' + CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 114),':','_') + '_R'

exec @rc = sp_trace_create @TraceID output, 2, @trace_file, @maxfilesize, NULL 
if (@rc != 0) goto finish

-- ***********************************************************************
-- ***  Configurar los Eventos y Campos deseados para la Traza creada  ***
-- ***********************************************************************
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 164, 8, @on
exec sp_trace_setevent @TraceID, 164, 9, @on
exec sp_trace_setevent @TraceID, 164, 10, @on
exec sp_trace_setevent @TraceID, 164, 14, @on
exec sp_trace_setevent @TraceID, 164, 26, @on
exec sp_trace_setevent @TraceID, 164, 34, @on
exec sp_trace_setevent @TraceID, 164, 11, @on
exec sp_trace_setevent @TraceID, 164, 35, @on
exec sp_trace_setevent @TraceID, 164, 12, @on
exec sp_trace_setevent @TraceID, 164, 28, @on
exec sp_trace_setevent @TraceID, 46, 8, @on
exec sp_trace_setevent @TraceID, 46, 9, @on
exec sp_trace_setevent @TraceID, 46, 10, @on
exec sp_trace_setevent @TraceID, 46, 14, @on
exec sp_trace_setevent @TraceID, 46, 26, @on
exec sp_trace_setevent @TraceID, 46, 34, @on
exec sp_trace_setevent @TraceID, 46, 11, @on
exec sp_trace_setevent @TraceID, 46, 35, @on
exec sp_trace_setevent @TraceID, 46, 12, @on
exec sp_trace_setevent @TraceID, 46, 28, @on
exec sp_trace_setevent @TraceID, 47, 8, @on
exec sp_trace_setevent @TraceID, 47, 9, @on
exec sp_trace_setevent @TraceID, 47, 10, @on
exec sp_trace_setevent @TraceID, 47, 14, @on
exec sp_trace_setevent @TraceID, 47, 26, @on
exec sp_trace_setevent @TraceID, 47, 34, @on
exec sp_trace_setevent @TraceID, 47, 11, @on
exec sp_trace_setevent @TraceID, 47, 35, @on
exec sp_trace_setevent @TraceID, 47, 12, @on
exec sp_trace_setevent @TraceID, 47, 28, @on


-- **************************
-- ***  Filtrar la traza  ***
-- **************************
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 0586943c-c605-4978-a62b-e36c2ca963af'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'tempdb'


-- ***************************
-- ***  Arrancar la traza  ***
-- ***************************
exec sp_trace_setstatus @TraceID, 1

finish: 
go