sp_autostats
Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.
Note In the context of this stored procedure, the term index refers to statistics on the table or view.
Syntax
sp_autostats [ @tblname = ] 'table_name'
[ , [ @flagc = ] 'stats_flag' ]
[ , [ @indname = ] 'index_name' ]
Arguments
[@tblname =] 'table_name'
Is the name of the table or view for which to display the automatic UPDATE STATISTICS setting. table_name is nvarchar(776), with no default. If index_name is supplied, Microsoft SQL Server enables the automatic UPDATE STATISTICS setting for that index.
[@flagc =] 'stats_flag'
Is whether the automatic UPDATE STATISTICS setting for the specified table, view, or index is enabled (ON) or disabled (OFF). stats_flag is varchar(10), with a default of NULL.
[@indname =] 'index_name'
Is the name of the index for which to enable or disable the automatic UPDATE STATISTICS setting. index_name is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
If stats_flag is specified, this procedure reports the action that was taken but returns no result set.
If stats_flag is not specified, sp_autostats returns this is the result set.
| Column name | Data type | Description |
|---|---|---|
| Index Name | varchar(60) | Name of the index. |
| AUTOSTATS | varchar(3) | Current automatic UPDATE STATISTICS setting: OFF or ON. |
| Last Updated | datetime | Date the statistics was last updated. |
Permissions
Only members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the table owner can execute sp_autostats.
Examples
A. Display the current status of all indexes for a table
This example displays the current statistics status of all indexes on the authors table.
USE pubs
EXEC sp_autostats authors
B. Enable automatic statistics for all indexes of a table
This example enables the automatic statistics setting for all indexes of the authors table.
USE pubs
EXEC sp_autostats authors, 'ON'
C. Disable automatic statistics for a specific index
This example disables the automatic statistics setting for the au_id index of the authors table.
USE pubs
EXEC sp_autostats authors, 'OFF', au_id