Last time table was updated
Show last read and last write for each table that was updated.
Last updated:
Warning: Review and test in a non-production environment before running.
-- last time database was updated.
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
WITH agg AS
(
SELECT
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);
-- last time a table was updated
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 4 desc,3 desc