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
Fig.1 Few columns of sys.tables table

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
Fig. 2 List of all modified tables in a Database

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;
Fig. 3 Recently modified tables in last 40 days from current date

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: