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 = pathfrom 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

selectObjectName

, DatabaseName

,StartTime

, EventClass

, EventSubClass

, ObjectType

, ServerName

, LoginName

, ApplicationName

, ''temp''

from ::fn_trace_gettable( @base_tracefilename,default )

whereEventClass in(46,47,164) and EventSubclass = 0and 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 asobj_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文件）