April 17, 2012

sp_createstats (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData


sys.sp_createstats(char @indexonly
, char @fullscan
, char @norecompute)


create procedure sys.sp_createstats
@indexonly char(9)= 'NO', -- Optional 'INDEXONLY' text - if present, then only the columns
-- covered by indexes are subject of statistics creation
@fullscan char(9)= 'NO', -- Optional 'FULLSCAN' text - if present, then the statistics
-- will be created with full scan rather than sampling
@norecompute char(12)= 'NO' -- Optional 'NORECOMPUTE' text - if present, then statistics
-- will not be updated automatically

-- NOTE: This sp will create statistics for *all* columns of all tables
-- which the user has the privilege to create stats on (sysadmin, dbo, owner).
-- The following columns are not considered
-- - first column of an index
-- - column which already has statistics
-- - XML column
-- - filestream column
-- - sparse column in a wide-table (a table with a sparse column set)
-- - nondeterministic computed columns
-- - imprecise computed columns

declare @sysadmin int
,@dbname sysname
select @indexonly = UPPER (@indexonly collate Latin1_General_CI_AS)

-- Validate input options
if (@indexonly not in ('INDEXONLY', 'NO') OR
UPPER(@fullscan) not in ('FULLSCAN', 'NO') OR
UPPER(@norecompute) not in ('NORECOMPUTE', 'NO'))
raiserror(15600,-1,-1, 'sys.sp_createstats')
return @@error

-- remember dbname
select @dbname = db_name()

-- create temporary table (column, index position)
create table #colpostab
( col_name sysname collate catalog_default,
col_pos int,

set nocount on

-- required for sp_createstats so it can create stats on on ICC/IVs
set ansi_warnings on
set ansi_padding on
set arithabort on
set concat_null_yields_null on
set numeric_roundabort off

declare @exec_stmt nvarchar(4000)
declare @tablename sysname
declare @columnname sysname
-- declare @shortcolumnname sysname
declare @indexname sysname
declare @uid int
declare @indid smallint
declare @position smallint
declare @table_id int
declare @table_type char(2)
declare @schema_name sysname
declare @numcols int -- number of eligible columns found
declare @msg nvarchar(388) -- adding two more chars for msg with 15654
declare @twopart_tablename nvarchar(517)
declare @timestamp varchar(17)
declare @wide_table bit

declare @tablename_header varchar(267)

declare ms_crs_tnames cursor local static for
select o.name, o.object_id, s.principal_id, s.name, o.type
from sys.objects o
join sys.schemas s on s.schema_id = o.schema_id
where o.type = 'U' or o.type = 'IT'

select @numcols = 0
-- cannot execute against R/O databases
if DATABASEPROPERTYEX(db_name(),N'Updateability')=N'READ_ONLY'
return (1)

open ms_crs_tnames
fetch next from ms_crs_tnames into @tablename, @table_id, @uid, @schema_name, @table_type

while (@@fetch_status <> -1)
-- check for table with disabled clustered index
if (1 = isnull((select is_disabled from sys.indexes where object_id = @table_id and index_id = 1), 0))
-- raiserror('Table ''%s'': cannot perform the operation on the table because its clustered index is disabled', -1, -1, @tablename)
-- note that we cannot use '%s' in the sqlerrorcodes.h as the same error is reused by sp_create|updatestats and they have
-- different formatting styles. This style is consistent with the rest of the messages in this SP
select @msg = ''''+ @dbname +'.'+ @schema_name +'.'+ @tablename + ''''
raiserror(15654, -1, -1, @msg)
-- filter out local temp tables, Hekaton tables, and tables for which current user has no permissions
-- Note that OBJECTPROPERTY returns NULL on type="IT" tables, thus we only call it on type='U' tables
if (
(@@fetch_status <> -2) and
(substring(@tablename, 1, 1) <> '#') and -- temp tables
((@table_type<>'U') or (0 = OBJECTPROPERTY(@table_id, 'TableIsInMemory'))) and -- Hekaton table
((is_member('db_owner')=1) or (is_member('ddl_admin')=1) or (is_member(user_name(@uid))=1) or (user_id() = @uid))) -- permissions
select @wide_table = objectproperty(@table_id, 'tablehascolumnset')

-- these are all columns for which the statistics will be created
declare ms_crs_cnames cursor local for select c.name from sys.columns c
where c.object_id = @table_id
and (type_name(c.system_type_id) not in ('xml'))
and c.name not in (select col_name from #colpostab where col_pos = 1)
and ((c.name in (select col_name from #colpostab)) or (@indexonly <> 'INDEXONLY'))
and (@wide_table=0 or is_sparse=0)
and (is_filestream=0)
and (is_computed = 0
or (is_computed = 1
and columnproperty(@table_id, c.name, 'isdeterministic') = 1
and columnproperty(@table_id, c.name, 'isprecise') = 1))

-- populate temporary table of all (column, index position) tuples for this table
truncate table #colpostab

-- for each index on the table, loop though all columns and insert rows
-- open cursor over indexes
declare ms_crs_ind cursor local static for
select stats_id, name from sys.stats
where object_id = @table_id order by stats_id

select @twopart_tablename = quotename( @schema_name , '[')+'.' + quotename( @tablename, '[')

open ms_crs_ind
fetch ms_crs_ind into @indid , @indexname

-- if an index exists
while @@fetch_status >= 0
-- if the index is not disabled and not a columnstore or if there is no entry in sys.indexes for current @indid
-- (we are looking at a statistic not an index) then we need to include the columns of that statistic in #colpostab
if not exists (select * from sys.indexes where object_id = @table_id and index_id = @indid and (is_disabled = 1 or type in (5, 6)))
-- every index has at least one column at position 1
insert into #colpostab values (index_col(@twopart_tablename,@indid,1),1)
-- now try position 2 and beyond....
select @columnname = index_col(@twopart_tablename, @indid, 2)
select @position = 2
while (@columnname is not null )
insert into #colpostab values (@columnname,@position)
select @position = @position +1
select @columnname = index_col(@twopart_tablename, @indid, @position)
-- next index
fetch ms_crs_ind into @indid , @indexname

close ms_crs_ind
deallocate ms_crs_ind

-- now go over all columns which are eligible for creating statistics
-- and are not first columns of any index
-- optionaly we test if they are covered by some index (as non-leading)

open ms_crs_cnames

fetch next from ms_crs_cnames into @columnname
if @@fetch_status < 0
select @msg = @dbname +'.'+ @schema_name +'.'+ @tablename
select @msg = @dbname +'.'+ @schema_name +'.'+ @tablename
raiserror(15018, -1, -1, @msg)

while @@fetch_status >= 0
select @numcols = @numcols +1
-- use the column name as the name for the statistics as well
select @exec_stmt = 'CREATE STATISTICS ' + quotename(@columnname, '[') + ' ON ' +
quotename( @schema_name ,'[')+'.' + quotename( @tablename, '[')+'('+ quotename( @columnname, '[')+')'
-- determining the correct suffix
if ((UPPER(@fullscan) = 'FULLSCAN') AND (UPPER(@norecompute) = 'NORECOMPUTE'))
select @exec_stmt = @exec_stmt + ' WITH FULLSCAN, NORECOMPUTE'
else if (UPPER(@fullscan) = 'FULLSCAN') select @exec_stmt = @exec_stmt + ' WITH FULLSCAN'
else if (UPPER(@norecompute) = 'NORECOMPUTE') select @exec_stmt = @exec_stmt + ' WITH NORECOMPUTE'
EXEC (@exec_stmt)
-- print 'Statement='+@exec_stmt
if (@@error = 0) -- otherwise the CREATE STATS will give a message
print ' ' + @columnname
fetch next from ms_crs_cnames into @columnname
close ms_crs_cnames
deallocate ms_crs_cnames
fetch next from ms_crs_tnames into @tablename, @table_id, @uid, @schema_name, @table_type

print ' '

deallocate ms_crs_tnames

if (object_id('[#colpostab]') is not null)
drop table [#colpostab]

return(0) -- sp_createstats


  1. What is 'Hekaton' tables?

  2. Internal table. Not known to the public ;)

  3. 'Hekaton' tables are in short: In Memory Tables, just google it there more info on it there. ;)
    PS sorry for the VERY late answer ;)


Total Pageviews