I use Microsoft SQL Server to maintain the data which received from other software (Smartplant Instrumentation). I want to know the last table which was updated and inserted by the data from the other software.
I used this query, but I didn't get the correct updated tables:
SELECT
SCHEMA_NAME(9),
name AS table_name,
create_date,
modify_date
FROM
sys.tables
WHERE
modify_date > DATEADD(DAY, -30, CURRENT_TIMESTAMP)
ORDER BY
modify_date DESC;
The query you ran only tracks changes to metadata, not the actual values.
You might want to to look at sys.dm_db_index_usage_stats, it could display the data changes in your tables:
select *
from sys.dm_db_index_usage_stats
where object_id = OBJECT_ID('your-table-name')
But the best way is to add a modify date to the tables you're monitoring, or implement Change Tracking or similar.
select top 10 name,modify_date
from sys.tables
where modify_date=(select max(modify_Date) from sys.tables)
this will return top to 10 tables recently updated tables
