SQL Server Is it possible to see which tables have had their data modified yesterday?

baubqpgj  于 2023-03-11  发布在  其他
关注(0)|答案(1)|浏览(103)

We have a Microsoft SQL Server database. We're not manually keeping track of when edits or updates to the data in the tables were made. Is there still a way to find out which tables have had their data updated within, say, the last 24 hours?

eit6fx6z

eit6fx6z1#

SQL Server does not track this information by default, though there is a way to see if any updates have been attempted since a given point in time using the dynamic management view sys.dm_db_index_usage_stats .

DECLARE @cutoff datetime = DATEADD(DAY, -1, GETDATE());

SELECT [schema] = s.name,
       [object] = o.name
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE EXISTS -- since we don't need 8 rows if there are 8 indexes
(
  SELECT 1 
    FROM sys.dm_db_index_usage_stats AS ius
    WHERE ius.[object_id] = o.[object_id]
    AND ius.last_user_update >= @cutoff
);

Some caveats:

  • This doesn't mean the data actually changed - this just reflects the latest update attempt.
  • You can't see just yesterday and not include today, or know if a row was only updated today and not yesterday - there isn't a running history, just the most recent update.
  • You can't see how many updates were attempted in that range, or how many rows were affected, never mind which rows.
  • You won't see any information for spatial or memory-optimized indexes.
  • This view gets cleared out on service restarts, failovers, offline/online, and some other events (version-specific) .

相关问题