sp_autostats (Transact-SQL)

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 iconTransact-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;

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_createstats (Transact-SQL)
System Stored Procedures (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance