SQL Server and its console, SSMS, provide quite useful report of schema changes made to specific database objects on a server and database level. This uses data from so called the default trace, a server internal log enabled in SQL Server by default that tracks a number of events, including DDL operations. SQL Server Management Studio is shipped with a Schema Changes History report that lists those operations. You can run it on server and database level.

Schema changes on server

To see schema changes in the server right click connection element in the object explorer and choose Reports > Standard Reports > Schema Changes History from context menu.

It will open a report like this. It shows list of DDL operations in the past couple of months with database name, database object name, time of operation, user who performed it and type of this operation (CREATE, ALTER or DROP).

Schema changes in the database

To see schema changes in the specific database right click it in the object explorer and choose Reports > Schema Changes History from context menu.

It will open a report showing objects modified in the past couple of months.

You can drill into database objects to see the specific DDL operation: object name, time of operation, user who performed it and type of this operation (CREATE, ALTER or DROP).

Disadvantages

Reports I showed you above have some informative value but for me (and our customers) they lack more details on what actually changed in those objects. That's why we added in our documentation tool a Schema Change Tracking feature presented below:

Alternative: Dataedo

Dataedo is a documentation tool that holds all metadata in its own repository (which is plain SQL Server database) and it has an option to track and report detailed schema changes each time you import database schema (you can schedule it in your OS).

On top of tracking you can also describe each change and share this report with complete data dictionary in HTML.

Try it yourself for free now