Have you ever wanted to know who made a schema change to your database? If so, that information is tracked in the default trace. The default trace is just a server-side profiler trace that gets started when SQL Server starts up, provided the default trace is enabled. If the default trace is enabled, then it is extremely simple to report single database, or instance wide schema changes, using SQL Server Management Studio.
To show the schema changes for a specific database, right click on your database and then when the menu is displayed hover over the “Reports” item. When the next menu is displayed, hover over the “Standard Report” option. On the “Standard Report” menu, you will see a “Schema Change History” menu option, as in the screenshot below:
Schema Change History
When you click on the “Schema Change History” report then all the schema changes that are contained in the default trace, for your database, will be displayed. Here is a sample of a schema change report for my DEMO database:
Schema Change Report
In the screenshot above you can see I created and altered a table named “MyNewTable”.
If you want to show all the schema changes for all the databases on your instance then right click on the instance name and select “Reports”, “Standard Reports” and then “Schema Changes”. When you do this, all schema changes for any database on your instance will be reported.
