sp_updatestats (Transact-SQL)
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_updatestats [ [ @resample = ] 'resample']
Arguments
- [ @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.
Return Code Values
0 (success) or 1 (failure)
Remarks
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.
Permissions
Requires membership in the sysadmin fixed server role, or ownership of the database (dbo).
Examples
The following example updates the statistics for tables in the AdventureWorks database.
USE AdventureWorks;
GO
EXEC sp_updatestats
See Also
Reference
Database Engine Stored Procedures (Transact-SQL)
ALTER DATABASE (Transact-SQL)
CREATE INDEX (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL)
sp_autostats (Transact-SQL)
sp_createstats (Transact-SQL)
sp_dbcmptlevel (Transact-SQL)
System Stored Procedures (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)