I have had a question from one of my collegues, how to find when was a table last modified.
Here is what I suggest to use to find when was tables schema changed and when was the last user update/delete on the table.
Script to find the last schema change date of a table, you will need to supply table name in the where clause. Run this query in the target database.
SELECT name AS TableName ,
modify_date AS Last_schema_Change
WHERE name = ‘<table>’
Script to find when the table data was last changed, here you will need to specify the database name and the table number.
SELECT OBJECT_NAME(OBJECT_ID) AS TableName ,
WHERE database_id = DB_ID(‘<databasename>’)
AND OBJECT_ID = OBJECT_ID(‘<tablename>’)