I'm sure this is an easy one for all you knowledgeable people out there.
How can I find the modification date of a database or table?
BTW, by modification date, I mean the last time it was updated or changed - not just when the table structure was modified as seems to be the case in sys.tables.
you can infer the last time (since the server was restarted) the last time a table was selected or updated by looking at the indexes
you cannot find out the information on a per row basis.
if you need that level of auditing, you can use some of the many functionalities in SQL Server: some examples(all
of which must be set up prior to data being changed):
SQL Audit, CDC, C2 Level Auditng, Add your own trace, exrtended events, DML triggers on specific tables,
for last touched index info on a table that i mentioned , here's a code example:
--based on the ideas from
;WITH ServerStarted AS
MIN(last_user_seek) AS first_seek,
MIN(last_user_scan) AS first_scan,
MIN(last_user_lookup) AS first_lookup
WHEN first_seek < first_scan AND first_seek < first_lookup
WHEN first_scan < first_seek AND first_scan < first_lookup
END AS usage_start_date
DB_NAME(database_id) AS TheDatabase,
OBJECT_NAME(object_id,database_id) As TheTableName,
MIN(ServerFirst.usage_start_date) AS usage_start_date,
MAX(x.last_read) AS last_read,
MAX(x.last_write) AS last_write
SELECT TheDatabase,TheTableName,last_user_seek AS last_read, NULL AS last_write FROM myCTE
SELECT TheDatabase,TheTableName,last_user_scan, NULL FROM myCTE
SELECT TheDatabase,TheTableName,last_user_lookup, NULL FROM myCTE
SELECT TheDatabase,TheTableName,NULL, last_user_update FROM myCTE
) AS x
CROSS JOIN ServerFirst
GROUP BY TheDatabase,TheTableName
ORDER BY TheDatabase,TheTableName
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!