How to get all the tables which are modified today in SQL Server

1

Hello,

I need to get list of the tables which are modified today (and the last 5 minutes) in SQL Server.

Thanks for your help.

2 
select * from sys.tables where modify_date >= cast(getdate() as DATE);
select * from sys.tables where modify_date >= dateadd(mi,-5,getdate());

That is specific to each database. If you need to get the list of tables used in all your databases, you will need to go through all databases. You can use the undocumented stored proc sp_msforeachdb for that.

1 Like
3

Just to be sure, do you mean where the structure of the table has been modified or do you mean tables that have suffered an INSERT, UPDATE, or DELETE?

1 Like
4 
SELECT name 'Table',modify_date 'Last Modified Date'
FROM sys.tables 
WHERE  DATEDIFF(D,modify_date, GETDATE()) < 1
1 Like
5

This (i.e. using a function on a column in a WHERE clause) has poor performance because it is not SARGable.

WHERE  modify_date  >= CAST(DATEADD(Day, -1, GetDate()) as DATE)

will do the same thing, but is SARGable and will not have the same performance implication.

I also recommend not using single quotes for column name aliases because they are confusable with string constants

1 Like
6

Hello Jeff,

I need to find when was the tables last updated for insert/delete/update with a desired time or day.
Thanks for your help.

Bill

7

It might be possible with some of system auditing function, but I'm not familiar with that.

If that isn't possible then I think you are out of luck. You would have to implement triggers, on all the tables, to log when a change was made.

On all the tables in our APP DBs we have columns for Create Date & User and Update Date and User, which makes it easy to report on "What has changed since XXX"

If this is a one-off (e.g. you are investigating Fraud or an Accident such as a bulk delete) AND your database is in FULL recovery model, then you could restore (to temporary database names) a backup from the start of the time interval of interest, and another from the end of that period - or just use the actual database for that purpose - and then "compare" the data in all the tables for differences. There are tools available which will make that comparison [i.e. between two databases of similar DDL structure]