Database schema last updated date
How to find out the last date and timestamp of schema updation in SQL Server database. I could locate the database created and last back up date from properties. But, couldn't find the last schema updated date. Is there a way to find this out?
Under SQL 2005/2008, you can use the modify_date in sys.objects to determine if the schema has been changed on a table. You can also use create_date/modify_date on other objects to determine if objects of concern have been created or modified. However, I'd agree with Cory that if you want to capture specific schema changes, DDL triggers would be the most appropriate solution.
One solution I can think of going forward is to use a DDL trigger to log any schema changes
See this article for more details: http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
This is not always correct beacause modify_date:--> Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered