Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_createstats

  No additional text.


Syntax

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)

	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 database_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 @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
		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

	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.
			if ((@@fetch_status <> -2) and (substring(@tablename, 1, 1) <> '#') and
				((is_member('db_owner')=1) or (is_member('ddl_admin')=1) or (is_member(user_name(@uid))=1) or (user_id() = @uid)))
			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)

				-- 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
					-- check if the index is not disabled
					-- if there is no entry (null) in sys.indexes for current @indid we are looking at the statistic (not index)
					-- we need to include the columns of that statistic in #colpostab
					if (1 <> isnull((select is_disabled from sys.indexes where object_id = @table_id and index_id = @indid), 0))
					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
	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

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash