Creates single-column statistics for all eligible columns for all user tables and internal tables in the current database. The new statistic has the same name as the column where it is created.
sp_createstats [ [ @indexonly = ] 'indexonly' ] [ , [ @fullscan = ] 'fullscan' ] [ , [ @norecompute = ] 'norecompute' ]
- [ @indexonly= ] 'indexonly'
Specifies that only the columns participating in an index should be considered for statistics creation. indexonly is char(9). The default is NO.
- [ @fullscan= ] 'fullscan'
Specifies that the FULLSCAN option is used with CREATE STATISTICS. If fullscan is omitted, the SQL Server 2005 Database Engine performs a default sample scan. fullscan is char(9). The default is NO.
- [ @norecompute= ] 'norecompute'
Specifies that automatic recomputation of statistics is disabled for the newly created statistics. norecompute is char(12). The default is NO.
Return Code Values
0 (success) or 1 (failure)
Columns that already have 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 previous restrictions. FULLSCAN is executed if fullscan is specified.
Statistics are not created on columns that are the leading columns of disabled indexes. When indexonly is specified, statistics are not created on a column in a disabled nonclustered index, unless that column is also used in another enabled index. sp_createstats ignores tables with a disabled clustered index.
Requires membership in the db_owner fixed database role.
The following example creates statistics for all eligible columns for all user tables in the current database.
The following example creates statistics for only the columns that are participating in an index.
EXEC sp_createstats 'indexonly';
Database Engine Stored Procedures (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL)
System Stored Procedures (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)