SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_help_selectivity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_help_selectivity]
GO

CREATE proc sp_help_selectivity

	@tablename	sysname,
	@col1		sysname,
	@col2		sysname = null,
	@col3		sysname = null,
	@col4		sysname	= null,
	@col5		sysname = null,
	@col6		sysname = null,
	@col7		sysname = null,
	@col8		sysname = null,
	@col9		sysname = null,
	@col10		sysname = null,
	@col11		sysname = null,
	@col12		sysname = null,
	@col13		sysname = null,
	@col14		sysname = null,
	@col15		sysname = null,
	@col16		sysname = null,
	@tableowner	sysname = 'dbo'

as

	----------------------------------------------------------------------------------------
	--  name:	sp_help_selectivity
	----------------------------------------------------------------------------------------
	--  author:	Doug Bass (doubas@earthlink.net or doubas@visto.com)
	----------------------------------------------------------------------------------------
	--  date:	17 September 1999
	----------------------------------------------------------------------------------------
	--
	--  purpose:	use to determine whether a column(s) would be a good candidate for an
	-- 		index.  compile this procedure in the master database so that it can
	--		be used on any user database.
	----------------------------------------------------------------------------------------
	--  notes:	displays a report showing "selectivity statistics" for the specified 
	--		table and column(s).  the higher the selectivity (the lower the rowhit
	--		percentage), the more likely a benefit will result from indexing the 
	--		column(s).
	--
	--		average selectivity
	--		-------------------	
	--		reports overall average selectivity for the column(s) provided
	--
	--		top 10 selectivity
	--		------------------
	--		reports the top ten >least< frequently occurring values (or combination
	--		of values) found in the column(s) provided. searches against these values
	--		will benefit the most from an index (ties are not shown)
	--
	--		bottom 10 selectivity
	--		---------------------
	--		reports the top ten >most< frequently occuring values (or combination of
	--		values) found in the column(s) provided.  searches against these values 
	--		will benefit the least from an index (ties are not shown)
	--
	----------------------------------------------------------------------------------------
	--		you are free to use or distribute this stored procedure as you see fit, 
	--		but please do not remove these comments.  no warranty implied.
	----------------------------------------------------------------------------------------

	declare @col_list	varchar(255),
		@numvalues	float,
		@numrows	float,
		@dbname		sysname

	set nocount on

	select @dbname	= db_name()

	if not exists(select * from tempdb..sysobjects where name = '##helpselectivity')
	begin

		if exists(	select 	* 
				from 	sysobjects 
				where 	id = object_id(@tableowner + '.' + @tablename)
				and	type = 'u'
		)
		begin

			----------------------------------------------------
			-- 		build column list
			----------------------------------------------------
			select	@col_list = @col1
			if @col2  is not null select @col_list = @col_list + ', ' + @col2
			if @col3  is not null select @col_list = @col_list + ', ' + @col3
			if @col4  is not null select @col_list = @col_list + ', ' + @col4
			if @col5  is not null select @col_list = @col_list + ', ' + @col5  
			if @col6  is not null select @col_list = @col_list + ', ' + @col6  
			if @col7  is not null select @col_list = @col_list + ', ' + @col7
			if @col8  is not null select @col_list = @col_list + ', ' + @col8
			if @col9  is not null select @col_list = @col_list + ', ' + @col9
			if @col10 is not null select @col_list = @col_list + ', ' + @col10
			if @col11 is not null select @col_list = @col_list + ', ' + @col11
			if @col12 is not null select @col_list = @col_list + ', ' + @col12
			if @col13 is not null select @col_list = @col_list + ', ' + @col13
			if @col14 is not null select @col_list = @col_list + ', ' + @col14
			if @col15 is not null select @col_list = @col_list + ', ' + @col15
			if @col16 is not null select @col_list = @col_list + ', ' + @col16
	
			----------------------------------------------------
			-- 	    create ##helpselectivity table
			----------------------------------------------------
			exec (	'select ' + @col_list + ', count(*) as rowhits, convert(numeric(9,8), 0) as selectivity' +
				'  into ##helpselectivity' +
			      	'  from ' + @tablename +
				' where 1 = 0 ' +
				' group by ' + @col_list +
				' order by rowhits, ' + @col_list
			)

			----------------------------------------------------
			-- 	    populate ##helpselectivity table
			----------------------------------------------------
			exec (	'insert into ##helpselectivity ' +
				'select ' + @col_list + ', count(*) as rowhits, convert(numeric(9,8), 0) as selectivity' +
			      	'  from ' + @tablename +
				' group by ' + @col_list +
				' order by rowhits, ' + @col_list
			)

			
			----------------------------------------------------
			--  get number of distinct value & total # of rows
			----------------------------------------------------
			select	@numvalues = count(*), @numrows = sum(rowhits)
			from	##helpselectivity
	
			----------------------------------------------------
			--		calculate selectivity
			----------------------------------------------------
			update  ##helpselectivity
			set	selectivity = rowhits / @numrows

			print	'--------------------------------------------------------------------------------------------'
			print	' 	                       S E L E C T I V I T Y   R E P O R T                           '
			print	'--------------------------------------------------------------------------------------------'
			print	''
	
			select 	"database" = convert(char(30), @dbname), 
				"owner"    = convert(char(30), suser_name(uid)), 
				"table"    = convert(char(30), object_name(id))
			from	sysobjects
			where	id = object_id(@tablename)
	
			print	''
			print	'columns tested'
			print	'--------------------------------------------------------------------------------------------'
			print	@col_list
	
			print	''
			print	''
	
			select  "average row hits" = avg(rowhits),
				"total # of rows" = sum(rowhits),
				"average selectivity" = convert(numeric(9,8), avg(rowhits) / @numrows)
			from	##helpselectivity
	
			set rowcount 10
		
			print '                              '
			print '                              '
			print '    TOP 10 (MOST SELECTIVE)   '
			print '------------------------------'
	
			select *
			  from	##helpselectivity t1
			 where   10 > (select 	count(*)
			  from	##helpselectivity t2
			 where	t2.rowhits < t1.rowhits)
			 order by t1.rowhits asc
	
			print '                              '
			print '                              '
			print ' BOTTOM 10 (LEAST SELECTIVE)  '
			print '------------------------------'
	
			select *
			  from	##helpselectivity t1
			 where   10 > (select 	count(*)
			  from	##helpselectivity t2
			 where	t2.rowhits > t1.rowhits)
			 order by t1.rowhits asc
	
			drop table ##helpselectivity

		end
			else	raiserror('Object "%s.%s" does not exist in database "%s" or is not a user table.', 
			   		  16, 1, @tableowner, @tablename, @dbname)

	end
		else raiserror('Cannot run multiple selectivity reports simultaneously', 16, 1)



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO