Monday, September 24, 2012

Schema Change History Report -- SQL Server Standard Reports

The schema change history report displays the changes made using DDL Commands. It tracks changes using default trace. If default trace is enabled on the server then we can run this report and view what are all the objects altered, deleted, created using this report.

To verify default trace option
sp_configure 'default trace enabled'

If the run value is 1 then it is enabled. To enable this option
sp_configure 'default trace enabled', 1
Reconfigure with override

This report has 6 columns to display history information

Databae Name – Displays name of the database on which changes happened.
Object Name – Displays name of the object on which changes made.
Type – Display type of object (ex : user defined object )
DDL Operation – Displays which action performed(Create / Alter / Drop)
Time – Display when DDL action was performed
Login Name – Displays the account name used to make changes.

No comments: