Script to get schema change history for specific day in SQL Server using server side trace

How to find schema change history  for specific day in SQL Server using server side trace

------------------------------------------------------------------------------------------

In order to get schema change history for specific date, Execute below query with specific .trc file.


By default .trc file resides in log folder of specific log folder of DB. 

SELECT * FROM:: fn_trace_gettable('E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_XX.trc', default )

WHERE EventClass in (46,47,164) AND EventSubclass = 0 AND DatabaseID <> 2

and startTime >= '2017-02-15 00:00:00.980' and startTime <='2017-02-15 23:59:00.980'

For different event class refer below link

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setevent-transact-sql?view=sql-server-2017


Here i have used event Classs

 46 --> 

46

Object:Created

Indicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.

 47 -->

47

Object:Deleted

Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.

 164 -->

164

Object:Altered

Occurs when a database object is altered.

Thanks for Reading