The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_createstats(char @indexonly, char @fullscan
, char @norecompute)
MetaData:
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 as -- 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')) begin raiserror(15600,-1,-1, 'sys.sp_createstats') return @@error end -- 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' begin raiserror(15635,-1,-1,N'sp_createstats') return (1) end open ms_crs_tnames fetch next from ms_crs_tnames into @tablename, @table_id, @uid, @schema_name, @table_type while (@@fetch_status <> -1) begin -- 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)) begin -- 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) end else begin -- 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 begin 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 begin -- 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))) begin -- 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 ) begin insert into #colpostab values (@columnname,@position) select @position = @position +1 select @columnname = index_col(@twopart_tablename, @indid, @position) end end -- next index fetch ms_crs_ind into @indid , @indexname end 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 begin select @msg = @dbname +'.'+ @schema_name +'.'+ @tablename raiserror(15013,-1,-1,@msg) end else begin select @msg = @dbname +'.'+ @schema_name +'.'+ @tablename raiserror(15018, -1, -1, @msg) end while @@fetch_status >= 0 begin 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 end close ms_crs_cnames deallocate ms_crs_cnames end end fetch next from ms_crs_tnames into @tablename, @table_id, @uid, @schema_name, @table_type end print ' ' raiserror(15020,-1,-1,@numcols) deallocate ms_crs_tnames if (object_id('[#colpostab]') is not null) begin drop table [#colpostab] end return(0) -- sp_createstats
What is 'Hekaton' tables?
ReplyDeleteInternal table. Not known to the public ;)
ReplyDelete'Hekaton' tables are in short: In Memory Tables, just google it there more info on it there. ;)
ReplyDeletePS sorry for the VERY late answer ;)