Pau Lo
asked on
MSSQL database last update
is there an easy way to determine the last time any table in an MSSQL database was updated? or a more detailed last update per table per database
ASKER
Would the modified time stamps (Windows) on the actual database files themselves be accurate or not really.
Not really. That would indicate that the file changed like it grew or something but nothing reliable for object changes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
index_usage_stats would tell you that an index was used, but wouldn't really tell you that the database or table was updated. And if the table has a clustered index, then there wouldn't be an index of 0 (HEAP).
I agree that it is (most likely) not accurate to reliable tell when a table has been changed last. But it might still suffice for getting some idea about activity, with some more polishing.
There is a modify_date in sys.tables for each table, but that is for when the table was last modified (column change, column added or any structural change with ALTER TABLE). This does not include data changes. This is more of a SQL Audit feature that you would have to turn on.