[ @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.

0 (success) or 1 (failure)

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.

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.

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

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.

A. Display the status of all statistics on a table

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

B. Enable AUTO_UPDATE_STATISTICS for all statistics on a table

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

C. Disable AUTO_UPDATE_STATISTICS for a specific index

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

Reference

Other Resources

Help and Information