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.
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;
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