Hi Friends,

In one of my recent trainings, when I was demonstrating the internals of STATISTICS, a participant asked if we can find out the last update date of the statistics.

Yes, we can do that using STATS_DATE.

Here is a small example.

Create a table and insert some test data.

Let us check the statistics.

You will observe that there are no statistics right now on the table. Let us execute a simple query to generate a statistics.

Now, check again:

You would see that statistics are created on LastName column. Now, the requirement is to find out the last/most recent update date for this statistics. The syntax for STATS_DATE is:

We can get the object id and stats id from sys.stats system catalog.

Run the sp_helpstats SP again (as shown above) and copy the stats name and execute the following query by replacing the stats name with yours.

You shall now have the object id and stats id which you can use in the STATS_DATE query as follows:

Here is the output on my system:

———————–

2011-10-28 14:07:38.290

(1 row(s) affected)

You can manually update the stats and run STATS_DATE again which will reflect the updated date.

———————–

2011-10-28 14:43:19.047

(1 row(s) affected)