sp_createstats (Transact-SQL)
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.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
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). 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)
Result Sets
None
Remarks
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.
Permissions
Requires membership in the db_owner fixed database role.
Examples
The following example creates statistics for all eligible columns for all user tables in the current database.
EXEC sp_createstats;
The following example creates statistics for only the columns that are participating in an index.
EXEC sp_createstats 'indexonly';
See Also
Reference
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)