sp_autostats

Transact-SQL Reference

Transact-SQL Reference

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

See Also

CREATE INDEX

CREATE STATISTICS

DBCC SHOW_STATISTICS

DROP STATISTICS

sp_createstats

sp_dboption

System Stored Procedures

UPDATE STATISTICS