CREATE            proc trace  @traceid int = 0, @setstatus int = -1 as
declare @events table (eventid int, [event] varchar(31))
declare @columns table (columnid int, [column] varchar(31))
declare @traces table (traceid int, [property] int, [tpdesc] nvarchar(245), check1 bit)
set nocount on

if @traceid < 0
begin
print 'Syntax for trace stored procedure.'
print 'trace [@traceid], [@setstatus]'
print 'The default for @traceid is 0.'
print 'The default for @setstatus is -1.'
print '@traceid @setstatus  action'
print '>0  not 0,1, or 2 list information for one trace'
print '0  not 0,1, or 2 list information all traces'
print '>0  0  stop one trace'
print '>0  1  start one trace'
print '>0  2  stop and delete one trace'
print '0  0  stop all traces'
print '0  1  start all traces'
print '0  2  stop and delete all traces'
print 'Example 1.  Stop all traces.'
print 'trace 0,0'
print 'Example 2.  Start trace number 1.'
print 'trace 1,1'
print 'Example 3.  Stop and delete all traces.'
print 'trace 0,2'
goto finish
end
Insert @events values (10,'RPC:Completed')
Insert @events values (11,'RPC:Starting')
Insert @events values (12,'SQL:BatchCompleted')
Insert @events values (13,'SQL:BatchStarting')
Insert @events values (14,'Login')
Insert @events values (15,'Logout')
Insert @events values (16,'Attention')
Insert @events values (17,'ExistingConnection')
Insert @events values (18,'ServiceControl')
Insert @events values (19,'DTCTransaction')
Insert @events values (20,'Login Failed')
Insert @events values (21,'EventLog')
Insert @events values (22,'ErrorLog')
Insert @events values (23,'Lock:Released')
Insert @events values (24,'Lock:Acquired')
Insert @events values (25,'Lock:Deadlock')
Insert @events values (26,'Lock:Cancel')
Insert @events values (27,'Lock:Timeout')
Insert @events values (28,'DOP Event')
Insert @events values (33,'Exception')
Insert @events values (34,'SP:CacheMiss')
Insert @events values (35,'SP:CacheInsert')
Insert @events values (36,'SP:CacheRemove')
Insert @events values (37,'SP:Recompile')
Insert @events values (38,'SP:CacheHit')
Insert @events values (39,'SP:ExecContextHit')
Insert @events values (40,'SQL:StmtStarting')
Insert @events values (41,'SQL:StmtCompleted')
Insert @events values (42,'SP:Starting')
Insert @events values (43,'SP:Completed')
Insert @events values (44,'Reserved ')
Insert @events values (45,'Reserved ')
Insert @events values (46,'Object:Created')
Insert @events values (47,'Object:Deleted')
Insert @events values (48,'Reserved')
Insert @events values (49,'Reserved')
Insert @events values (50,'SQL Transaction')
Insert @events values (51,'Scan:Started')
Insert @events values (52,'Scan:Stopped')
Insert @events values (53,'CursorOpen')
Insert @events values (54,'Transaction Log')
Insert @events values (55,'Hash Warning')
Insert @events values (58,'Auto Update Stats')
Insert @events values (59,'Lock:Deadlock Chain')
Insert @events values (60,'Lock:Escalation')
Insert @events values (61,'OLE DB Errors')
Insert @events values (67,'Execution Warnings')
Insert @events values (68,'Execution Plan')
Insert @events values (69,'Sort Warnings')
Insert @events values (70,'CursorPrepare')
Insert @events values (71,'Prepare SQL')
Insert @events values (72,'Exec Prepared SQL')
Insert @events values (73,'Unprepare SQL')
Insert @events values (74,'CursorExecute')
Insert @events values (75,'CursorRecompile')
Insert @events values (76,'CursorImplicitConversion')
Insert @events values (77,'CursorUnprepare')
Insert @events values (78,'CursorClose')
Insert @events values (79,'Missing Column Statistics')
Insert @events values (80,'Missing Join Predicate')
Insert @events values (81,'Server Memory Change')
Insert @events values (82,'User Configurable')
Insert @events values (83,'User Configurable')
Insert @events values (84,'User Configurable')
Insert @events values (85,'User Configurable')
Insert @events values (86,'User Configurable')

Insert @events values (87,'User Configurable')
Insert @events values (88,'User Configurable')
Insert @events values (89,'User Configurable')
Insert @events values (90,'User Configurable')
Insert @events values (91,'User Configurable')
Insert @events values (92,'Data File Auto Grow')
Insert @events values (93,'Log File Auto Grow')
Insert @events values (94,'Data File Auto Shrink')
Insert @events values (95,'Log File Auto Shrink')
Insert @events values (96,'Show Plan Text')
Insert @events values (97,'Show Plan ALL')
Insert @events values (98,'Show Plan Statistics')
Insert @events values (99,'Reserved')
Insert @events values (100,'RPC Output Parameter')
Insert @events values (101,'Reserved')
Insert @events values (102,'Audit Statement GDR')
Insert @events values (103,'Audit Object GDR')
Insert @events values (104,'Audit Add/Drop Login')
Insert @events values (105,'Audit Login GDR')
Insert @events values (106,'Audit Login Change Property')
Insert @events values (107,'Audit Login Change Password')
Insert @events values (108,'Audit Add Login to Server Role')
Insert @events values (109,'Audit Add DB User')
Insert @events values (110,'Audit Add Member to DB')
Insert @events values (111,'Audit Add/Drop Role')
Insert @events values (112,'App Role Pass Change')
Insert @events values (113,'Audit Statement Permission')
Insert @events values (114,'Audit Object Permission')
Insert @events values (115,'Audit Backup/Restore')
Insert @events values (116,'Audit DBCC')
Insert @events values (117,'Audit Change Audit')
Insert @events values (118,'Audit Object Derived Permission')

insert @columns values (1,'TextData')
insert @columns values (2,'BinaryData')
insert @columns values (3,'DatabaseID')
insert @columns values (4,'TransactionID')
insert @columns values (5,'Reserved')
insert @columns values (6,'NTUserName')
insert @columns values (7,'NTDomainName')
insert @columns values (8,'ClientHostName')
insert @columns values (9,'ClientProcessID')
insert @columns values (10,'ApplicationName')
insert @columns values (11,'SQLSecurityLoginName')
insert @columns values (12,'SPID')
insert @columns values (13,'Duration')
insert @columns values (14,'StartTime')
insert @columns values (15,'EndTime')
insert @columns values (16,'Reads')
insert @columns values (17,'Writes')
insert @columns values (18,'CPU')
insert @columns values (19,'Permissions')
insert @columns values (20,'Severity')
insert @columns values (21,'EventSubClass')
insert @columns values (22,'ObjectID')
insert @columns values (23,'Success')
insert @columns values (24,'IndexID')
insert @columns values (25,'IntegerData')
insert @columns values (26,'ServerName')
insert @columns values (27,'EventClass')
insert @columns values (28,'ObjectType')
insert @columns values (29,'NestLevel')
insert @columns values (30,'State')
insert @columns values (31,'Error')
insert @columns values (32,'Mode')
insert @columns values (33,'Handle')
insert @columns values (34,'ObjectName')
insert @columns values (35,'DatabaseName')
insert @columns values (36,'Filename')
insert @columns values (37,'ObjectOwner')
insert @columns values (38,'TargetRoleName')
insert @columns values (39,'TargetUserName')
insert @columns values (40,'DatabaseUserName')
insert @columns values (41,'LoginSID')
insert @columns values (42,'TargetLoginName')
insert @columns values (43,'TargetLoginSID')
insert @columns values (44,'ColumnPermissionsSet')

insert into @traces
 SELECT [traceid], [property]
      , [tpdesc]  =
 case
 when [property] = 1 and [value] = 1 then N' produces a rowset.'
 when [property] = 1 and [value] = 2 then
    N' creates a new file when max file size is reached.'
 when [property] = 1 and [value] = 3 then
    N' create a new file when max file size is reached and produce a rowset.'
 when [property] = 1 and [value] = 4 then N' shuts down the trace on an error.'
 when [property] = 1 and [value] = 5 then N' produces a rowset and shutdown on error.'
 when [property] = 1 and [value] = 6 then
    N' creates a new file when max file size is reached and shuts down on an error.'
 when [property] = 1 and [value] = 7 then
    N' creates a new file when max file size is reached, produces a rowset and shuts down on an error.'
 when [property] = 1 and [value] = 8 then N' is a Blackbox trace.'
 when [property] = 2 then N' results are in file ' + cast([value] as nvarchar(245)) + N'.trc.'
 when [property] = 3 then N' max file size is ' + cast([value] as nvarchar(5)) + N' megabytes.'
 when [property] = 4 and [value] is not null then
    N' automatically stops on ' + cast([value] as nvarchar(25)) + '.'
 when [property] = 4 and [value] is null then N' does not automatically stop on any date and time.'
 when [property] = 5 and [value] = 0 then N' is stopped.'
 when [property] = 5 and [value] = 1 then N' is running.'
 end
       ,0
 FROM :: fn_trace_getinfo(0) order by [property] desc

if (select count(*) from @traces) < 1
 begin
  select 'No traces exist.' as [Trace Information]
  goto finish
 end

if @traceid > 0
 begin
 update @traces set check1 = 1 where [traceid] != @traceid
 if (select count(*) from @traces where [traceid] = @traceid) < 1
  begin
   select 'Trace number ' + cast(@traceid as varchar(3)) + ' does not exist.' as [Trace Information]
   goto finish
  end
 end

if (@setstatus = 0 or @setstatus = 1 or @setstatus = 2) goto changestatus

if @traceid < 1 select 'Trace number '  + cast([traceid] as nvarchar(3)) + [tpdesc]
                    as 'TRACE STATUS' from @traces where [property] = 5

while (select count(*) from @traces where check1 = 0) > 0
 begin
  select top 1 @traceid = traceid from @traces where check1 = 0
  select N'Trace number ' + cast(traceid as nvarchar(3)) + [tpdesc] as 'PROPERTY'
     from @traces where traceid = @traceid order by [property] DESC
  select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' is tracing event '
         + cast(a.eventid as nvarchar(3)) + ', ' + b.[event] + '.' as 'EVENT'
    from ::fn_trace_geteventinfo(@traceid) a
    left join @events b on a.eventid = b.eventid
   group by a.eventid, b.[event]
  select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' is tracing column '
         + cast(a.columnid as nvarchar(3)) + ', ' + b.[column] + '.' as 'COLUMN'
    from ::fn_trace_geteventinfo(@traceid) a
    left join @columns b on a.columnid = b.columnid
   group by a.columnid, b.[column]
    if (select count(*) from ::fn_trace_getfilterinfo(@traceid)) > 0
    select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' filters on column '
          + cast(b.columnid as varchar(3)) +', ' + b.[column] + ', '
         + case a.comparison_operator
        when 0 then ' Equal '
        when 1 then ' Not Equal '
        when 2 then ' Greater Than '
        when 3 then ' Less Than '
        when 4 then ' Greater Than Or Equal '
        when 5 then ' Less Than Or Equal '
        when 6 then ' LIKE '
        when 7 then ' NOT LIKE '
        end
       + cast(value as varchar(50)) + ' '
         + case a.logical_operator
           when 0 then 'and '
           when 1 then 'or '
           end
          as 'FILTER'
      from ::fn_trace_getfilterinfo(@traceid) a
      left join @columns b on a.columnid = b.columnid
    else
      begin
        print 'Trace number '  + cast(@traceid as nvarchar(3)) + ' has no filters.'
        print ''
      end
  update @traces set check1 = 1 where traceid = @traceid
 end
goto finish
changestatus:
while (select count(*) from @traces where check1 = 0) > 0
  begin
  select top 1 @traceid = traceid from @traces where check1 = 0
  update @traces set check1 = 1 where [traceid] = @traceid
    if @setstatus = 0
      begin
       exec sp_trace_setstatus @traceid, 0
        select 'Trace number ' + cast(@traceid as varchar(3)) + ' is stopped.' as [Trace Information]
      end
    if @setstatus = 1
      begin
        exec sp_trace_setstatus @traceid, 1
        select 'Trace number ' + cast(@traceid as varchar(3)) + ' is running.' as [Trace Information]
      end
  if @setstatus = 2
   begin
        exec sp_trace_setstatus @traceid, 0
        exec sp_trace_setstatus @traceid, 2
        select 'Trace number ' + cast(@traceid as varchar(3)) + ' was stopped and deleted.' as [Trace Information]
   end
 end
finish:
set nocount off

GO