CODEWITHSUNDEEP
sql-serversql-server-2008
pencilslate
pencilslate

Reputation: 13068

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?

Upvotes: 0

Views: 5226

Answers (3)

Saurabh Sinha
Saurabh Sinha

Reputation: 1373

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

Upvotes: 0

data jockey
data jockey

Reputation: 131

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.

Upvotes: 3

Cory
Cory

Reputation: 12824

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

Upvotes: 0

Related Questions