Schema Changes History数据从哪里来？

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中抓取了创建删除和更改三个事件，然后展现给我们。 所以如果我们Disable了Default trace file，就无法从这张Report中查询到数据。

另外如果DDL发生在当前Default trace file开始之前也是显示不出来的，但是我们可以将上面的脚本修改一下，直接用当时的Trace File就可以获得相关信息了（SQL Server 默认会保存5份Trace文件）