sp_autostats (Transact-SQL)

Article
12/03/2008

Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a specified 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.

Topic link icon Transact-SQL Syntax Conventions

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, SQL Server 2005 modifies the automatic UPDATE STATISTICS setting for that index.

[ @flagc= ] 'stats_flag'
Specifies the automatic UPDATE STATISTICS setting for the table, view, or index:

ON = enabled

OFF = disabled

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 the following 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.

Remarks

If the specified index is disabled, or the specified table has a disabled clustered index, an error message will be displayed.

Permissions

To change the automatic UPDATE STATISTICS setting requires membership in the db_owner fixed database role, or ALTER permission on table_name.To display the automatic UPDATE STATISTICS setting requires membership in the public role.

Examples

A. Displaying the current status of all indexes for a table

The following example displays the current statistics status of all indexes on the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product';

B. Enabling automatic statistics for all indexes of a table

The following example enables the automatic statistics setting for all indexes of the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product', 'ON';

C. Disabling automatic statistics for a specific index

The following example disables the automatic statistics setting for the AK_Product_Name index of the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product', 'OFF', AK_Product_Name;