use db_name;

declare @enable int; 
select top  1  @enable = convert(int,value_in_use) from sys.configurations where name = 'default trace enabled'  
if @enable =  1  
begin 
        declare @d1 datetime; 
        declare @diff int; 
        declare @curr_tracefilename varchar( 500 );  
        declare @base_tracefilename varchar( 500 );  
        declare @indx int ; 
        declare @temp_trace table (
                obj_name nvarchar( 256 )
        ,       obj_id int
        ,       database_name nvarchar( 256 )
        ,       start_time datetime
        ,       event_class int
        ,       event_subclass int
        ,       object_type int
        ,       server_name nvarchar( 256 )
        ,       login_name nvarchar( 256 )
        ,       user_name nvarchar( 256 )
        ,       application_name nvarchar( 256 )
        ,       ddl_operation nvarchar( 40 ) 
        );
        
        select @curr_tracefilename = path from sys.traces where is_default =  1  ;  
        set @curr_tracefilename = reverse(@curr_tracefilename) 
        select @indx  = PATINDEX('%\%', @curr_tracefilename) 
        set @curr_tracefilename = reverse(@curr_tracefilename) 
        set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc'; 
        
        insert into @temp_trace 
        select ObjectName
        ,       ObjectID
        ,       DatabaseName
        ,       StartTime
        ,       EventClass
        ,       EventSubClass
        ,       ObjectType
        ,       ServerName
        ,       LoginName
        ,       NTUserName
        ,       ApplicationName
        ,       'temp' 
        from ::fn_trace_gettable( @base_tracefilename, default )  
        where EventClass in ( 46 , 47 , 164 ) and EventSubclass =  0   and DatabaseID = db_id() ; 

        update @temp_trace set ddl_operation = 'CREATE' where event_class =  46 ;
        update @temp_trace set ddl_operation = 'DROP' where event_class =  47 ;
        update @temp_trace set ddl_operation = 'ALTER' where event_class =  164 ; 

        select @d1 = min(start_time) from @temp_trace 
        set @diff= datediff(hh,@d1,getdate())
        set @diff=@diff/ 24 ; 
        
        select  @diff as difference
        ,       @d1 as date
        ,       object_type as obj_type_desc 
        ,       (dense_rank() over (order by obj_name,object_type ) )% 2  as l1 
        ,       (dense_rank() over (order by obj_name,object_type,start_time ))% 2  as l2
        ,       *
        from @temp_trace where object_type not in ( 21587 ) -- don't bother with auto-statistics as it generates too much noise
        order by start_time desc;
end else 
begin 
        Select top  0   1  as difference,    1  as date,  1  as obj_type_desc,   1  as l1,  1  as l2,        1  as obj_name,   1  as obj_id,  1  as database_name,         1  as start_time,  1  as event_class,  1  as event_subclass,  1  as object_type,  1  as server_name,  1  as login_name,  1  as user_name,  1  as application_name,  1  as ddl_operation  
end