Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
sp_updatestats (Transact-SQL)
Collapse the table of content
Expand the table of content

sp_updatestats (Transact-SQL)

Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.

Topic link icon Transact-SQL Syntax Conventions

sp_updatestats [ [ @resample = ] 'resample']

[ @resample =] 'resample'

Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS statement. New statistics will inherit the sampling ratio from the old statistics. If 'resample' is not specified, sp_updatestats updates statistics by using the default sampling. This parameter is varchar(8) with a default value of NO.

0 (success) or 1 (failure)

sp_updatestats effectively executes UPDATE STATISTICS, by specifying the ALL keyword, against 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. Statistics on disabled nonclustered indexes are also updated by sp_updatestats. sp_updatestats ignores tables with a disabled clustered index.

In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.

For databases with a compatibility level below 90, executing sp_updatestats resets the automatic UPDATE STATISTICS setting for all indexes and statistics on every table in the current database. For more information, see sp_autostats (Transact-SQL). For databases with a compatibility level of 90 or higher, sp_updatestats preserves the automatic UPDATE STATISTICS setting for any particular index or statistics.

Requires membership in the sysadmin fixed server role, or ownership of the database (dbo).

The following example updates the statistics for tables in the AdventureWorks database.

USE AdventureWorks;
EXEC sp_updatestats 

Community Additions

© 2015 Microsoft