SQL Server: Show the date a table was last modified

I’m currently developing an online application using classic asp (no negative comments please), with the data layer provided by SQL server 2008.  One of the tables I use is refreshed from another source regularly but not every day, which means that the data displayed might not always be up to date.

I wanted a simple way to display the date the data was last updated so that I could let my users know that the data they are viewing is accurate as of that date.

Turns out this can be done with a query, and the SQL is really quite simple.  Here it is:-

SELECT modify_date FROM sys.tables WHERE name = 'YOUR TABLE NAME HERE';

This is really useful, especially if you want to let your users know when the last time modifications were made to your table.

I’ve read a number of other ways to do this, but I think this is the most simple and doesn’t require any kind of admin permissions on the server.  Good stuff!

3 Comments

  • NOPE
    June 17, 2013 - 12:57 pm | Permalink

    DOES NOT WORK. SAYS THE COLUMN DOESN’T EXIST…..!

  • Bhargav
    June 28, 2013 - 7:25 pm | Permalink

    Its giving DDL modification not Data

  • Mark
    August 11, 2014 - 10:56 pm | Permalink

    Exactly, this gives the last time the Table schema was modified, not the last time a row was added/deleted/updated.

  • Leave a Reply

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