sql server Default Trace 可以简单定位对象的增删改

Default Trace扑捉一些关键性信息，
auditing events，database events，error events，full text events，object creation，object deletion，object alteration。
一：检查跟踪环境是否开启
1）查询Default Trace是否开启
select * from sys.configurations where configuration_id = 1568
2）开启Trace
sp_configure "show advanced options", 1;
go
reconfigure;
go
sp_configure "default trace enabled", 1;
go
reconfigure;
go

二：查询历史记录

1）获取当前跟踪文件的路径
把文件载入表中
select * from ::fn_trace_getinfo(0)
2）查询记录

select
  loginname,
  loginsid,
  spid,
  hostname,
  applicationname,
  servername,
  databasename,
  objectname,
  a.category_id,
  b.name,
  textdata,
  starttime,
  eventclass,
  eventsubclass, --0 begin,1 commit
  a.name
from ::fn_trace_gettable('D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_4.trc',0)
  inner join sys.trace_events a
          on eventclass = trace_event_id
  inner join sys.trace_categories b
          on a.category_id = b.category_id
where databasename = 'tiger_test' and
      objectname='tiger_table' and
       a.category_id = 5 and   --5 object
       a.trace_event_id = 46   --46 create，47 drop，164 alter