Find Recently Modified Tables In SQL Server Database

Introduction

SQL Server maintains a table to keep information about each tables that are created in appropriate databases. Its a System Table called as sys.tables. For every table created to the database, a new one record is created in the sys.tables table which contains information such as table name, object id of table, created date, modified date, etc.

SYS.TABLES

SELECT * FROM sys.tables

Find Recently Modified Tables

Now, we are interested only in the tables which are recently modified. For that we need the columns as Schema Name, Table Name, Modified Date. So lets query those from the sys.tables table.

SELECT SCHEMA_NAME(SCHEMA_ID) AS SchemaName,
       name AS TableName,
       create_date AS CreatedDate,
       modify_date AS ModifiedDate
FROM sys.tables

We can also apply predicators to filter the data, like most recently modified table by passing the date duration. Eg: Recently modified tables in last 5 days or modified tables in last one month likewise.

SELECT SCHEMA_NAME(SCHEMA_ID) AS SchemaName,
       name AS TableName,
       create_date AS CreatedDate,
       modify_date AS ModifiedDate
FROM sys.tables
WHERE modify_date > DATEADD(DAY, -40, CURRENT_TIMESTAMP)
ORDER BY modify_date DESC;

Conclusion

Thus we can find the most recently modified tables in SQL Server Database. I hope you all found this article much useful. Please share your feedback in the comments sections.