How to get All the Tables which are Modified in last few days in Sql Server?

Scripts, Sql Server, , , ,

We can write a query like below to get the all the Tables which are modified using an ALTER statement in last 10 days. A Table is also considered as modified when an Index on the table is Created or Altered.

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

Note: If a table is never altered after it’s creation then modify_date column value in the sys.objects will be it’s creation date.

7 thoughts on “How to get All the Tables which are Modified in last few days in Sql Server?”

  2. I enjoyed reading this article.
    Thanks for educating the community and appreciate your volunteership.

    In this direction you could write examples on all_table, views, all_views and other sys.xxxx

    Thanks a bunch

    Reply

  5. one database (SQL )table is created on 3/oct and we modified the data on 5/oct.
    i have window forms in vb using visual studio in that i have one calendar and datagridview
    if i click on calendar 3/oct it should open that date created and modified data,it should not show 5/oct modified data.Please tell me how?

    Reply

  7. modify_date also changes when a clustered index on the table or view is created or altered. or index rebuild by sqlserver .

    Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%.

    SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *