Table of Contents

After executing the script (given on below link) to rebuild all table indexes on SQL Server, how do we know whether it is updated or not?

SQL Script for rebuilding all the tables' indexes

Now the problem is that SQL Server does not store the information when all the indexes were rebuilt. However, it stores the information on when was the last time statistics were updated. There is a system table (sys.stats) which can be queried to know this. Whenever an index rebuilt operation happens on a database, it updates sys.stats with last statistics updates. So for a given index, it can be checked when the index was rebuilt by checking the last stats updated date. However, what if we only updated statistics, but haven't rebuilt indexes?

Below is the query you can use to get the details on last stats updated date. This query works on the AdventureWorks database:

USE AdventureWorks;
GO
  
SELECT name AS Stats,
STATS_DATE(object_id, stats_id) AS LastStatsUpdate
FROM sys.stats
WHERE object_id = OBJECT_ID('Sales.SalesOrderDetail')
and left(name,4)!='_WA_';
  
GO

You can find the detailed post on this topic at http://insqlserver.com/Blog/how-check-when-index-rebuild-update-statistics-last-happened-sql-server 


See Also


Other Languages

This article is also available the following languages: