Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
sp_updatestats executes UPDATE STATISTICS, by specifying the ALL keyword, on all user-defined and internal tables in the database. sp_updatestats displays messages that indicate its progress. When the update is completed, it reports that statistics have been updated for all tables.
sp_updatestats updates statistics on disabled nonclustered indexes and does not update statistics on disabled clustered indexes.
sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.
For databases with a compatibility level below 90, executing sp_updatestats does not preserve the latest NORECOMPUTE setting for specific statistics. For databases with a compatibility level of 90 or higher, sp_updatestats does preserves the latest NORECOMPUTE option for specific statistics. For more information about disabling and re-enabling statistics updates, see Using Statistics to Improve Query Performance.
The following example updates the statistics for tables in the AdventureWorks2008R2 database.
USE AdventureWorks2008R2; GO EXEC sp_updatestats