sp_autostats (Transact-SQL)
Displays or changes the automatic statistics update option, AUTO_UPDATE_STATISTICS, for an index, a statistics object, a table, or an indexed view.
For more information about the AUTO_UPDATE_STATISTICS option, see ALTER DATABASE SET Options (Transact-SQL) and Statistics.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_autostats [ @tblname = ] 'table_or_indexed_view_name'
[ , [ @flagc = ] 'stats_value' ]
[ , [ @indname = ] 'statistics_name' ]
Arguments
[ @tblname= ] 'table_or_indexed_view_name'
Is the name of the table or indexed view to display the AUTO_UPDATE_STATISTICS option on. table_or_indexed_view_name is nvarchar(776), with no default.[ @flagc= ] 'stats_value'
Updates the AUTO_UPDATE_STATISTICS option to one of these values:ON = ON
OFF = OFF
When stats_flag is not specified, display the current AUTO_UPDATE_STATISTICS setting. stats_value is varchar(10), with a default of NULL.
[ @indname= ] 'statistics_name'
Is the name of the statistics to display or update the AUTO_UPDATE_STATISTICS option on. To display the statistics for an index, you can use the name of the index; an index and its corresponding statistics object have the same name.statistics_name is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
If stats_flag is specified, sp_autostats 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 or statistics. |
AUTOSTATS |
varchar(3) |
Current value for the AUTO_UPDATE_STATISTICS option. |
Last Updated |
datetime |
Date of the most recent statistics update. |
The result set for a table or indexed view includes statistics created for indexes, single-column statistics generated with the AUTO_CREATE_STATISTICS option and statistics created with the CREATE STATISTICS statement.
Remarks
If the specified index is disabled, or the specified table has a disabled clustered index, an error message is displayed.
Permissions
To change the AUTO_UPDATE_STATISTICS option requires membership n the db_owner fixed database role, or ALTER permission on table_name.To display the AUTO_UPDATE_STATISTICS option requires membership in the public role.
Examples
A. Display the status of all statistics on a table
The following displays the status of all statistics on the Product table.
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product';
GO
B. Enable AUTO_UPDATE_STATISTICS for all statistics on a table
The following enables the AUTO_UPDATE_STATISTICS option for all statistics on the Product table.
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product', 'ON';
GO
C. Disable AUTO_UPDATE_STATISTICS for a specific index
The following example disables the AUTO_UPDATE_STATISTICS option for the AK_Product_Name index on the Product table.
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product', 'OFF', AK_Product_Name;
GO
See Also
Reference
ALTER DATABASE SET Options (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)
System Stored Procedures (Transact-SQL)