sp_autostats (T-SQL)

Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table in the current database.

Note In the context of this stored procedure, the term index refers to statistics on the table.

Syntax

sp_autostats [@tblname =] 'table_name'
    [, [@flagc =] 'stats_flag']
    [, [@indname =] 'index_name']

Arguments

[@tblname =] 'table_name'

Is the name of the table 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 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.

Permissions

Execute permissions default to the table owner.

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