Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_helpstats

  No additional text.


Syntax

create procedure sys.sp_helpstats
	@objname nvarchar(776),		-- the table to check for statistics
	@results nvarchar(5) = 'STATS'	-- 'ALL' returns indexes & stats, 'STATS' returns just stats
as
	-- PRELIM
	set nocount on
	declare 	@objid int,			-- the object id of the table
			@indid smallint,	-- the index id of an index
			@indname sysname,
			@keys nvarchar(2078),-- string build index key list, length = (16*max_id_length)+(15*2)
			@dbname	sysname,
			@i int,
			@thiskey sysname,
			@curs	cursor

	-- Check to see that the object names are local to the current database.
	select @dbname = parsename(@objname,3)
	if @dbname is null
		select @dbname = db_name()
	else if @dbname <> db_name()
		begin
			raiserror(15250,-1,-1)
			return (1)
		end

	-- Check to see the the table exists and initialize @objid.
	select @objid = object_id(@objname, 'local')
	if @objid is NULL
	begin
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
	end
	If UPPER(@results) <> 'STATS' and UPPER(@results)<> 'ALL'
	begin
    		raiserror(N'Invalid option: %s', 1, 1, @results)
		return (1)
	end

	If UPPER(@results) = 'STATS'
	begin
		set @curs = cursor local fast_forward READ_ONLY for
			select stats_id, name from sys.stats
			where object_id = @objid
			  and IndexProperty(@objid, name, 'IsStatistics') = 1 -- User created & auto-created stats
	end
	else
	begin
		set @curs = cursor local fast_forward READ_ONLY for
			select stats_id, name from sys.stats
			where object_id = @objid -- Indexes, User created & auto-created stats
	end

	open @curs
	fetch @curs into @indid, @indname

	-- IF NO STATISTICS, QUIT
	if @@fetch_status < 0
	begin
		deallocate @curs
		If UPPER(@results) = 'STATS'
		begin
			raiserror(15574,-1,-1) --'Object does not have any statistics.'
		end
		else
		begin
			raiserror(15575,-1,-1) --'Object does not have any indexes or statistics.'
		end
	return (0)
	end
	-- create temp table
	CREATE TABLE #spstattab
	(
		stats_name			sysname	collate database_default NOT NULL,
		stats_keys			nvarchar(2078)	collate database_default NOT NULL
	)

	-- Now check out each statistics set, figure out its keys and
	--	save the info in a temporary table that we'll print out at the end.
	while @@fetch_status >= 0
	begin
		-- First we'll figure out what the keys are.

		select @keys = index_col(@objname, @indid, 1),
				@i = 2, @thiskey = index_col(@objname, @indid, 2)
		while (@thiskey is not null )
		begin
			select @keys = @keys + ', ' + @thiskey, @i = @i + 1
			select @thiskey = index_col(@objname, @indid, @i)
		end

		-- INSERT ROW FOR INDEX
		insert into #spstattab values (@indname, @keys)

		-- Next index
		fetch @curs into @indid, @indname
	end
	deallocate @curs

	-- DISPLAY THE RESULTS
	select
		'statistics_name' = stats_name,
		'statistics_keys' = stats_keys
	from #spstattab
	order by stats_name

return (0) -- sp_helpstats

----------------------- sp_helptext 

raiserror(15339,-1,-1,'sys.sp_helptext')

 
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