SQL Server提供了Schema Changes History report可以用来追踪DDL相关信息。 但是Schema Changes History report的数据是从哪里来的呢？

首先我启动SQL Profiler trace然后打开Schema Changes History report（Management studio->Report->Standard report->Schema Changes History report）

从Profiler trace我看到下面的SQL 语句：

        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
        ,       DatabaseName
        ,       StartTime
        ,       EventClass
        ,       EventSubClass
        ,       ObjectType
        ,       ServerName
        ,       LoginName
        ,       ApplicationName
        ,       ''temp''
        from ::fn_trace_gettable( @base_tracefilename, default )
        where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

        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
        ,       *
        from @temp_trace where object_type not in (21587)
        order by start_time desc
end

我们看一下event_class 46,47,164代表什么：

 select trace_event_id,name from sys.trace_events where trace_event_id in ('46','47','164')

trace_event_id name
-------------- --------------------------------------------------------
46             Object:Created
47             Object:Deleted
164            Object:Altered

(3 row(s) affected)

从上面的内容我们可以看到这个Report只是从Default trace中抓取