sp_createstats (T-SQL)

Creates single-column statistics for all eligible columns for all user tables in the current database. The new statistic has the same name as the column on which it is created. Computed columns and columns of the ntext, text, or image data types cannot be specified as statistics columns. Columns already having statistics are not touched (for example, the first column of an index or a column with explicitly created statistics). A CREATE STATISTICS statement is executed for each column that satisfies the above restrictions. FULLSCAN is executed if fullscan is specified.

Syntax

sp_createstats [[@indexonly =] 'indexonly']
        [,[@fullscan =] 'fullscan']
        [,[@norecompute =] 'norecompute']

Arguments

[@indexonly =] 'indexonly'

Specifies that only the columns participating in an index should be considered for statistics creation. indexonly is char(9), with a default of NO.

[@fullscan =] 'fullscan'

Specifies that the FULLSCAN option is used with the CREATE STATISTICS statement. If fullscan is omitted, Microsoft® SQL Server™ performs a default sample scan. fullscan is char(9), with a default of NO.

[@norecompute =] 'norecompute'

Specifies that automatic recomputation of statistics is disabled for the newly created statistics. norecompute is char(12) with a default of NO.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Permissions

Permissions default to members of the sysadmin fixed server role and the db_owner fixed database role.

Examples

This example creates statistics for all eligible columns for all user tables in the current database.

EXEC sp_createstats

This example creates statistics for only the columns participating in an index.

EXEC sp_createstats 'indexonly'

See Also