3 out of 3 rated this helpful - Rate this topic

sp_autostats (Transact-SQL)

SQL Server 2005

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;

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

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD