/*
*********************************************************************************************
NAME: install.sql 
MERGE MERGE PERFORMANCE TROUBLESHOOTING
DESCRIPTION: 
OWNER: alejanmi
CREATED BY: rtom
CREATED ON: 11/27/01
CHANGES HISTORY:
	03/13/2002 alejanmi 
**********************************************************************************************
*/

/* Drop Stored Procedures Section */
raiserror('        Drop Stored-Procedures/Functions Section', 0,1)

if object_id('proc_merge_diagnostic_report') is not null
begin
	drop procedure proc_merge_diagnostic_report
end
GO

if object_id('proc_help_suggest_index') is not null
begin
	drop procedure proc_help_suggest_index
end
GO

if object_id('proc_replace_section') is not null
begin
	drop procedure proc_replace_section
end
GO

if object_id('proc_help_gen_full_report') is not null
begin
	drop procedure proc_help_gen_full_report
end
GO

raiserror('        Creating Functions and Stored Procedures', 0,1)
GO

raiserror('        CREATE PROCEDURE proc_merge_diagnostic_report ', 0,1)
GO

/**************************************************************************************************
PROCEDURE proc_merge_diagnostic_report
DESCRIPTION: READS MSMERGE_HISTORY TABLE AND DISPLAYS LATEST OR ALL PERFORMANCE STAT FOR AN AGENT
PARAMETERS: 
HOW TO USE IT: exec merge_stats @job_spec ='last', @agent_id=46 --@debug='debug' 
HISTORY CHANGES:
	rtom : Created x-xx-2001. (Sphinx)
	alejanmi : Modified in 3-12-2002:
***************************************************************************************************/

/*
 Example:
	View Result Sets   :	exec proc_merge_diagnostic_report 'db_pub'
	View Full Report   :	exec proc_merge_diagnostic_report 'db_pub', 1
	Keep Report Tables :	exec proc_merge_diagnostic_report 'db_pub', 0, 1
	
				select * from [##tbl_uniquekey_report]
				select * from [##tbl_index_report]
				select * from [##tbl_existing_index_report]
				select * from [##tbl_filter_nesting_report]
*/


/*
 To Run Diagnostic:
	1. Run this script in your publication db
	2. exec proc_merge_diagnostic_report 'Publication_Name'
 
 Results:
 	1st result set: 
		Lets you know what the suggested 'join unique key' option 
		value should be per join filter. If the expected value is 'null' 
		that means you have multiple join article columns specified in 
		the filter.
 	2nd result set:
		Suggests new indexes to be added to your tables on a per
		subsetfilter/joinfilter basis (the IsJoinFilter col will let you 
		know if it is a subsetfilter or join). You can have more than two 
		suggestions (1 for article 1 for join article column) per filter 
		since you may have and, or, subqueries etc. Here you are also given
		a rowcount and a cardinality value so you can determine whether the 
		index will be useful. If you have a large number of rows and a
		cardinality close to 1 then the index will be useful. Low number of 
		rows or a cardinality less than .50 may not benefit from an added
		index. 
	3rd result set: 
		Shows you all of the current indexes that exist and may be used for your filter
	4th result set: 
		Contains information on filter nesting levels

 Options:
	@full_report:
		Setting this option to 1 will generate a full report including scripts to 
		apply the suggestions to your publication. This is a 'proof of concept'
		and isn't completed. In any case, the output is just an idea of what we can
		extract from the collected data and also presents a hierarchical view of the filters.

	@keep_report_tables:
		Allows you to keep the global temp report tables so that you can query them directly
		after running the proc.

*/


-- 
-- Procedure:
-- 	proc_help_gen_full_report
-- 
-- Description:
--	Generate a full report based on global temp tables generated by the proc_merge_diagnostic_report.
--	Included in the full report is a script to apply the suggested changes to the publication. This is
--	not completed and is just a 'proof of concept' type proc.
--
-- Parameters:
--	@publication 		sysname		publication name
--
create procedure proc_help_gen_full_report
(
	@publication	sysname
) 
as
begin
	declare @RetCode		int
	declare @RetErrorMsg		varchar(4000)
	declare @ReportCursorState	int	-- 0-deallocated 1-declared 2-opened 3-closed
	declare @PrintString		varchar(8000)
	declare @OptionValue		varchar(8000)
	declare @FilterName		sysname
	declare @FilterID		int
	declare @TableColID		int
	declare @TableName		sysname
	declare @Query 			varchar(8000)
	declare @Article 		sysname
	declare @NickName		int
	declare @JoinArticle 		sysname
	declare @ArtCol			sysname
	declare @JoinCol		sysname
	declare @JoinUniqueKey		int
	declare @JoinUniqueKeyExpected	int
	declare	@IsJoinFilter		bit
	declare @Cardinality 		float
	declare @IsIndexed		bit
	declare @Filter 		varchar(1000)
	declare @CurrentRowCount	int
	declare @CurrentLevel 		int
	declare @NewLevel 		int
	declare @Totals			int
		
	select @RetCode  		= 0
	select @RetErrorMsg		= '(unspecified error)'
	select @ReportCursorState	= 0
	select @FilterName		= null
	select @FilterID		= null
	select @TableColID		= null
	select @TableName		= null
	select @Query 			= null
	select @Article 		= null
	select @NickName		= null
	select @JoinArticle 		= null
	select @ArtCol			= null
	select @JoinCol			= null
	select @JoinUniqueKey		= 0
	select @JoinUniqueKeyExpected	= 0
	select @IsIndexed		= 0
	select @Filter 			= null
	select @CurrentRowCount		= 0
	select @CurrentLevel 		= 0
	select @NewLevel 		= 0
	select @Totals			= 0


	/*
 	 * Create any needed temp tables
	 */ 	
	if object_id('tempdb..#tbl_print_order') is not null
	begin
		drop table #tbl_print_order
	end
	
	-- create tables for printing order
	create table #tbl_print_order
	(
		TableName	sysname,
		TotalLevels	int		null
	)

	/* 
	 * Script out suggestions for the JoinUniqueKey property of merge join filters
	 */
	
	select @PrintString = 'Suggesting changes for the ''JoinUniqueKey'' values on Merge Article Join Filters'
	print replicate('-', len(@PrintString) + 6)
	print '-- ' + @PrintString + ' --'
	print replicate('-', len(@PrintString) + 6)
	
	declare report_cursor cursor fast_forward for
		select * 
		from ##tbl_uniquekey_report
		where ExpectedJoinUniqueKey is not null
			and ActualJoinUniqueKey <> ExpectedJoinUniqueKey
			and CurrentRowCount > 0
	select @ReportCursorState = 1

	open report_cursor
	select @ReportCursorState = 2

	fetch next from report_cursor into @FilterID, @FilterName, @Filter, @JoinUniqueKey, @JoinUniqueKeyExpected, @JoinArticle, @JoinCol, @CurrentRowCount
	if @@fetch_status <> 0	
	begin
		print '--'
		print '-- No necessary changes for the JoinUniqueKey property of your join filters detected!'
		print '--'
	end

	while @@fetch_status = 0
	begin 
		if @JoinUniqueKeyExpected = 0
		begin
			select @OptionValue = 'false' 
		end
		else
		begin
			select @OptionValue = 'true'
		end
		
		print '--'
		print '-- FilterID       : ' + cast(@FilterID as varchar)
		print '-- FilterName     : ' + @FilterName
		print '-- Filter         : ' + @Filter
		print '-- Join Article   : ' + @JoinArticle
		print '-- Join Column    : ' + @JoinCol
		print '-- Current # Rows : ' + cast(@CurrentRowCount as varchar)
		print '-- Suggestion     : Change JoinUniqueKey from ' + cast(@JoinUniqueKey as varchar) + ' to ' + cast(@JoinUniqueKeyExpected as varchar)
		print ' '
		
		if charindex(' 8.0', @@version) > 0
		begin
			print 'exec sp_changemergefilter @publication = ''' + @publication + '''' + 
									', @article = ''' + @JoinArticle + '''' +
									', @filtername = ''' + @FilterName + '''' +
									', @property = ''join_unique_key''' + 
									', @value = ''' + @OptionValue + '''' +
									', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1'
		end
		else
		begin
			print 'exec sp_changemergefilter @publication = ''' + @publication + '''' + 
									', @article = ''' + @JoinArticle + '''' +
									', @filtername = ''' + @FilterName + '''' +
									', @property = ''join_unique_key''' + 
									', @value = ''' + @OptionValue + ''''
		end
		print 'go'

		fetch next from report_cursor into @FilterID, @FilterName, @Filter, @JoinUniqueKey, @JoinUniqueKeyExpected, @JoinArticle, @JoinCol, @CurrentRowCount
	end
	
	close report_cursor
	select @ReportCursorState = 3

	deallocate report_cursor
	select @ReportCursorState = 0
	
	/* 
	 * Script out warning for the JoinUniqueKey property of merge join filters
	 */
	
	select @PrintString = 'Warnings for the ''JoinUniqueKey'' values on Merge Article Join Filters'
	print replicate('-', len(@PrintString) + 6)
	print '-- ' + @PrintString + ' --'
	print replicate('-', len(@PrintString) + 6)
	
	declare report_cursor cursor fast_forward for
		select * 
		from ##tbl_uniquekey_report
		where ExpectedJoinUniqueKey is null
			or (CurrentRowCount = 0 and ActualJoinUniqueKey = 1)
			
	select @ReportCursorState = 1

	open report_cursor
	select @ReportCursorState = 2

	fetch next from report_cursor into @FilterID, @FilterName, @Filter, @JoinUniqueKey, @JoinUniqueKeyExpected, @JoinArticle, @JoinCol, @CurrentRowCount
	if @@fetch_status <> 0	
	begin
		print '--'
		print '-- No warnings for the JoinUniqueKey property of your join filters detected!'
		print '--'
	end

	while @@fetch_status = 0
	begin 
		print '--'
		print '-- FilterID       : ' + cast(@FilterID as varchar)
		print '-- FilterName     : ' + @FilterName 
		print '-- Filter         : ' + @Filter
		print '-- Join Article   : ' + @JoinArticle
		print '-- Join Column    : ' + @JoinCol
		print '-- Current # Rows : ' + cast(@CurrentRowCount as varchar)
		if @CurrentRowCount = 0 
		begin
			print '-- Warning        : The current row count is 0 and you have set the JoinUniqueKey property to true!'
			print '--                  Please ensure that any future inserts to this table.column will be unique or change the property to false.'
		end
		else
		begin
			print '-- Warning        : Unable to detect Join Article Column!'
			print '--                  Please ensure that the value set for the JoinUniqueKey property is true only if the Join Article.Column'
			print '--                  values will be unique across all rows in the table.'
		end
		
		print '--'
		
		fetch next from report_cursor into @FilterID, @FilterName, @Filter, @JoinUniqueKey, @JoinUniqueKeyExpected, @JoinArticle, @JoinCol, @CurrentRowCount
	end
	
	close report_cursor
	select @ReportCursorState = 3

	deallocate report_cursor
	select @ReportCursorState = 0

	/* 
	 * Script out suggestions for covering indexes
	 */
	
	select @PrintString = 'Suggesting ''create index'' statements for ArticleTable.Columns involved in filtering'
	print replicate('-', len(@PrintString) + 6)
	print '-- ' + @PrintString + ' --'
	print replicate('-', len(@PrintString) + 6)
	
	declare report_cursor cursor fast_forward for
		select * 
		from ##tbl_index_report
		where Cardinality >=  0.5
			and CurrentRowCount > 0
	select @ReportCursorState = 1

	open report_cursor
	select @ReportCursorState = 2

	fetch next from report_cursor into @FilterID, @FilterName, @Filter, @IsJoinFilter, @Article, @ArtCol, @Cardinality, @CurrentRowCount
	if @@fetch_status <> 0	
	begin
		print '--'
		print '-- No new index suggestions for your filters detected!'
		print '--'
	end

	while @@fetch_status = 0
	begin 
		print '--'
		print '-- NickName/FilterID: ' + cast(@FilterID as varchar)
		print '-- Name             : ' + @FilterName 
		print '-- Filter           : ' + @Filter
		print '-- Article Table    : ' + @Article
		print '-- Table Column     : ' + @ArtCol
		print '-- Cardinality      : ' + cast(@Cardinality as varchar)
		print '-- Current # Rows   : ' + cast(@CurrentRowCount as varchar)
		print ' '
		print 'create index Mrg_Diag_' + @Article + '_' + @ArtCol + '_' + cast(replace(newid(), '-', '') as varchar) + ' on ' + @Article + ' (' + @ArtCol + ')'
		print 'go'

		fetch next from report_cursor into @FilterID, @FilterName, @Filter, @IsJoinFilter, @Article, @ArtCol, @Cardinality, @CurrentRowCount
	end
	
	close report_cursor
	select @ReportCursorState = 3

	deallocate report_cursor
	select @ReportCursorState = 0

	/* 
	 * Script out suggestions for covering indexes
	 */
	
	select @PrintString = 'Warnings for indexes for ArticleTable.Columns involved in filtering'
	print replicate('-', len(@PrintString) + 6)
	print '-- ' + @PrintString + ' --'
	print replicate('-', len(@PrintString) + 6)
	
	declare report_cursor cursor fast_forward for
		select * 
		from ##tbl_index_report
		where Cardinality <  0.5
			or CurrentRowCount = 0
	select @ReportCursorState = 1

	open report_cursor
	select @ReportCursorState = 2

	fetch next from report_cursor into @FilterID, @FilterName, @Filter, @IsJoinFilter, @Article, @ArtCol, @Cardinality, @CurrentRowCount
	if @@fetch_status <> 0	
	begin
		print '--'
		print '-- No warnings for indexes detected!'
		print '--'
	end

	while @@fetch_status = 0
	begin 	
		print '--'
		print '-- NickName/FilterID: ' + cast(@FilterID as varchar)
		print '-- Name             : ' + @FilterName 
		print '-- Filter           : ' + @Filter
		print '-- Article Table    : ' + @Article
		print '-- Table Column     : ' + @ArtCol
		print '-- Cardinality      : ' + cast(@Cardinality as varchar)
		print '-- Current # Rows   : ' + cast(@CurrentRowCount as varchar)

		if @CurrentRowCount = 0
		begin
			
			print '-- Warning          : This column currently does not have an index but, the current row count for this column'
			print '--                    is 0. Based on this number, no index can be safely suggested. You will need to manually'
			print '--                    determine the cardinality value will be (distinct column/row count) and if it is > 0.5'
			print '--                    you may want to add an index.'
		end
		else
		begin
			print '-- Warning          : This column currently does not have an index but, the cardinality for this column is'
			print '--                    low and may not benifit from an index. If the cardinality will change in the future'
			print '--                    (closer to >= 0.5) it may be beneficial to add an index.'
		end

		fetch next from report_cursor into @FilterID, @FilterName, @Filter, @IsJoinFilter, @Article, @ArtCol, @Cardinality, @CurrentRowCount
	end
	
	close report_cursor
	select @ReportCursorState = 3

	deallocate report_cursor
	select @ReportCursorState = 0
	
	/*
	 * Filter Nesting levels
	 */

	-- get nesting level info
	print '--'
	select @PrintString = 'Filter Nesting Level Information'
	print replicate('-', len(@PrintString) + 6)
	print '-- ' + @PrintString + ' --'
	print replicate('-', len(@PrintString) + 6)

	-- set the printing order
	insert into #tbl_print_order
	(
		TableName,
		TotalLevels
	)select TableName, count(*) - 1 
		from ##tbl_filter_nesting_report
		group by TableName
		having count(*) > 0
		order by count(*), TableName
	
	select @Totals = count(*) 
	from #tbl_print_order

	print '--'
	print '-- Total Num Articles  	: ' + cast(@Totals as varchar)
	
	select @Totals = count(*) 
	from #tbl_print_order
	where TotalLevels > 0

	print '-- Filtered Articles	: ' + cast(@Totals as varchar)

	-- declare cursor to begin printing out articles nesting levels
	declare report_cursor cursor fast_forward for
		select tpo.TotalLevels, tfn.*
		from ##tbl_filter_nesting_report tfn,
			#tbl_print_order tpo
		where tfn.TableName = tpo.TableName
		order by tpo.TotalLevels desc, tpo.TableName asc, tfn.NestLevel
	select @ReportCursorState = 1

	open report_cursor
	select @ReportCursorState = 2
	
	fetch next from report_cursor into @Totals, @Article, @TableName, @CurrentLevel, @NickName, @FilterID, @Filter, @IsJoinFilter
	while @@fetch_status = 0
	begin
	
		select @Article = quotename(@Article)

		if @Totals = 0
		begin
			print '--'
			print '--'
			print '-- Table Name   : ' + @TableName
			print '-- Article      : ' + @Article
			print '-- Num of Levels: ' + cast(@Totals as varchar)
			print '-- Comments     : This table is not filtered.'
		end
		else if @CurrentLevel = 0 
		begin	
			print '--'
			print '--'
			print '-- Table Name   : ' + @TableName
			print '-- Article      : ' + @Article
			print '-- Num of Levels: ' + cast(@Totals as varchar)
		end
		else
		begin
			if @IsJoinFilter = 1
			begin
				print '--      FilterID: ' + cast(@FilterID as varchar) + replicate(char(9), @CurrentLevel + 1) + @Filter
			end
			else
			begin
				print '--      NickName: ' + cast(@FilterID as varchar) + replicate(char(9), @CurrentLevel + 1) + @Filter
			end
		end

		fetch next from report_cursor into @Totals, @Article, @TableName, @CurrentLevel, @NickName, @FilterID, @Filter, @IsJoinFilter
	end

	close report_cursor
	select @ReportCursorState = 3

	deallocate report_cursor
	select @ReportCursorState = 0		

	select @RetCode  = 0
	goto Done
Err_Handler:
	
	-- close or deallocate cursors as necessary
	if @ReportCursorState = 2
	begin 
		close report_cursor
	end

	if @ReportCursorState in (1, 2, 3)
	begin 
		deallocate report_cursor
	end

	select @RetCode  = 1

Done:
	return @RetCode
end
GO


-- 
-- Procedure:
-- 	proc_replace_section
-- 
-- Description:
-- 	Helper function to replace a section in a string. The proc uses 1 string as a
-- 	begin marker a 2nd string for the end marker and replaces this entire section
-- 	with a user input replace string.
--
-- Parameters:
--	@orig_str 	varchar(8000)			original string to have a section replaced
--	@out_str	varchar(8000) output		output string (string with replace performed)
--	@start_str	varchar(100) 		= '''' 	the starting marker between which we will start replace
--	@end_str	varchar(100) 		= ''''	the end marker between which we will end replace
--	@replace_str	varchar(8000)		= ' '	replace string
--
create procedure proc_replace_section
(
	@orig_str 	varchar(8000),
	@out_str	varchar(8000) output,
	@start_str	varchar(100) 		= '''',
	@end_str	varchar(100) 		= '''',
	@replace_str	varchar(8000)		= ' '
)
as
begin
	declare @RetCode	int
	declare @StartPos 	int
	declare @EndPos 	int
	declare @EndMarkLen	int
	
	-- init variables
	select @RetCode		= 0
	select @StartPos 	= 0
	select @EndPos 		= 0
	select @EndMarkLen	= 0

	-- get length of the end marker since we will need to use that multiple times
	select @EndMarkLen = len(@end_str)
	if @@error <> 0 goto Err_Handler
	
	-- get the starting position of the first instance of the starting marker
	select @StartPos = charindex(@start_str, @orig_str, 1)
	if @@error <> 0 goto Err_Handler
	
	-- get the starting position of the first instance of the end marker, here we make
	-- sure to add the length of the end marker so it's included in the replace
	select @EndPos = @EndMarkLen + charindex(@end_str, @orig_str, @StartPos + 1) - 1
	if @@error <> 0 goto Err_Handler
		
	while (@StartPos > 0 and @StartPos < @EndPos)
	begin
		-- perform the section replace
		select @orig_str = replace(@orig_str, substring(@orig_str, @StartPos, @EndPos - @StartPos + 1), @replace_str)
		if @@error <> 0 goto Err_Handler
		
		-- look for the next instance of the start marker
		select @StartPos = charindex(@start_str, @orig_str, 1)
		if @@error <> 0 goto Err_Handler
		
		-- look for the next instance of the end marker, again add the end marker length
		select @EndPos = @EndMarkLen + charindex(@end_str, @orig_str, @StartPos + 1) - 1
		if @@error <> 0 goto Err_Handler
	end
	
	-- if we got here everything is fine so set retcode to 0 and set output value
	select @RetCode = 0
	select @out_str = @orig_str
	goto Done
Err_Handler:
	-- error occured so we must set the output val to null and set retcode to indicate failure
	select @RetCode = 1
	select @out_str = null
Done:		
	return @RetCode
end
GO


-- 
-- Procedure:
-- 	proc_help_suggest_index
-- 
-- Description:
-- 	Helper function to aid in suggesting the indexes that may need to be added to an
-- 	article filter. The function relies on global temp tables that should have been 
-- 	created by the calling procedure (should be proc_merge_diagnostic_report). The
--	suggestions are based on searching a filter for the following:
--		Any instance of any_article_table_name.column_name
--		Any instance of column_name for the Article on which the filter has been added
--	Keep in mind that like, >, <, in, etc. operators are ignored so in some cases the
--	suggestions may be off. This is why we will provide the filter within the suggestion
--	table so the user can quickly scan to see if the index may help.
-- 
-- Parameters:
--	@Article	sysname		Article name for which indexes should be suggested
--	@FilterID 	int		Filter ID to be inserted in the suggestion table
--	@Filter		varchar(1000)	Filer string to be searched for columns and used in suggestion
--	@IsJoinFilter 	bit		Is this a join filter? to be inserted in the suggestion table
--
create procedure proc_help_suggest_index
(
	@TableName	sysname,
	@FilterID 	int,
	@Filter		varchar(1000),
	@IsJoinFilter 	bit
)
as
begin
	declare @RetCode 	int
	declare @RetErrorMsg	varchar(4000)
	declare @TmpFilter	varchar(1000)
	
	-- init variables
	select @RetCode 	= 0
	select @RetErrorMsg	= '(unspecified error)'
	select @TmpFilter	= null
	
	-- Perform simple param check
	select @TableName	= rtrim(ltrim(@TableName))
	select @Filter		= rtrim(ltrim(@Filter))
	
	if @TableName is null or @TableName = ''
	begin
		select @RetErrorMsg = 'Table name can not be null or empty string!'
		goto Err_Handler
	end

	if @Filter is null or @Filter = ''
	begin
		select @RetErrorMsg = 'Filter string can not be null or empty string!'
		goto Err_Handler
	end

	-- replace any section in the filter that is in between single quote we 
	-- perform this replace because anything in single quote is a string which 
	-- we want to avoid checking for table.columname pairs when suggesting indexes
	exec @RetCode = proc_replace_section @Filter, @TmpFilter output
	if @RetCode <> 0 or @@error <> 0 
	begin
		select @RetErrorMsg = 'Failed to parse out strings from filter!'
		goto Err_Handler
	end

	-- remove [, ], " quoted identifier characters in filter
	select @TmpFilter = replace(replace(replace(@TmpFilter, ']', ''), '[', ''), '"', '')
	
	-- replace all valid operators with ' ' to make search possible 
	select @TmpFilter = replace(@TmpFilter, Operator, ' ') from ##tmp_valid_operators
	
	-- add space to front and end of string to make search possible
	select @TmpFilter = ' ' + @TmpFilter + ' '	
		
	-- this is the main section for index suggestion. we basically check to see if
	-- any article.column, join_article.column or (main article) column string exists 
	-- in the filter. We include all of these table.columns in the suggest list
	insert into ##tbl_index_suggestion 
			( 
				FilterID, 
				TableColID, 
				IsJoinFilter,
				IsIndexed
			) 
		select @FilterID, 
			tac.TableColID, 
			@IsJoinFilter,
			0
		from ##tbl_article_column tac 
		where  @TmpFilter like '% ' + tac.TableName + '.' + tac.ColumnName + ' %'		
			or (@TableName = tac.TableName and 
				@TmpFilter like '% ' + tac.ColumnName + ' %')
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured during suggestion inserts!'
		goto Err_Handler
	end

	-- if we got here everything is good so set retcode to 0
	select @RetCode = 0
	goto Done
Err_Handler:
	-- error occured so we must set retcode to indicate failure and print out error
	select @RetCode = 1
	raiserror(@RetErrorMsg, 16, 1)	
	
Done:
	return @RetCode
end
GO


-- 
-- Procedure:
-- 	proc_merge_diagnostic_report
-- 
-- Description:
--	This is the main procedure that goes through all articles in a given publication,
--	checks subsetfilter and joinfilter clauses for missing indexes and incorrectly set
--	'join unique key values that maybe useful.
--	
-- Parameters:
--	@publication 		sysname		publication name
--	@full_report		bit	= 0	0 - default. Generate the 3 resultsets
--						1 - generate a full report in text instead of the resultset report. 
--							When set, the proc analyzes the normal resultsets
--							that are returned when set to 0 and reports extended 
--							information/suggestions. In addition, scripts for suggested
--							covering indexes will be generated. @keep_report_tables 
--							is forcefully set to 0 when @full_report = 1 and the resultsets
--							are NOT displayed.
--	@keep_report_tables	bit	= 0	keep the report tables so you can use them at a later time.
--						if this option is set to 1 no result sets are returned but,
--						the following global temp tables are left intact at the end 
--						of the procedure:
--							table: [##tbl_uniquekey_report] 	for join unique key suggestions
--							table: [##tbl_index_report] 		for filter index suggestions
--							table: [##tbl_existing_index_report] 	for current filter index listings
--							table: [##tbl_filter_nesting_report] 	for filter nesting levels
--
create procedure proc_merge_diagnostic_report
(
	@publication 		sysname,
	@full_report		bit	= 0,
	@keep_report_tables	bit	= 0
)
as
begin
	set nocount on 

	declare @RetCode		int			-- return code used for sp error checks and set at the end for local sp return val (set at end an in error handling)
	declare @RetErrorMsg		varchar(4000)		-- Error message to raise on failure set throughout the proc
	declare @FilterCursorState	int			-- 0-deallocated 1-declared 2-opened 3-closed
	declare @FilterID		int			-- filter id which can be either a join filterid or article nickname
	declare @TableColID		int			-- TableColID for a global temp table
	declare @TableName		sysname			-- Article TableName
	declare @Query 			varchar(8000)		-- Query to be dynamically executed
	declare @NickName		int			-- Table Article nickname from sysmergearticles
	declare @JoinTableName 		sysname			-- Join Article TableName
	declare @ArtCol			sysname			-- Article column
	declare @JoinCol		sysname			-- A Join Articles column
	declare @JoinUniqueKey		int			-- JoinUniqueKey value found in sysmergesubsetfilters table
	declare @IsJoinFilter		bit			-- 0-SubsetFilter 1-Join Filter
	declare @IsIndexed		bit			-- 0-No index found for Table.Column 1-An index exists for Table.Column
	declare @Filter 		varchar(1000)		-- var to hold a filter expresion
	declare @Count			int			-- Counting variable
	declare @NewLevel 		int			-- New nesting level
	declare @CurrentLevel 		int			-- The current nesting level we are working on

	-- init variables
	select @RetCode			= 0
	select @RetErrorMsg		= '(unspecified error)'
	select @FilterCursorState	= 0
	select @FilterID		= null
	select @TableColID		= null
	select @TableName		= null
	select @Query 			= null
	select @NickName		= null
	select @JoinTableName 		= null
	select @ArtCol			= null
	select @JoinCol			= null
	select @JoinUniqueKey		= 0
	select @IsJoinFilter		= 0
	select @IsIndexed		= 0
	select @Filter 			= null
	select @Count			= 0
	select @CurrentLevel 		= 0
	select @NewLevel 		= 0

	/* check permissions and make sure db is published for merge replication */
	
	-- check permissions
    	if is_srvrolemember('sysadmin') <> 1 and is_member('db_owner') <> 1
	begin   
		select @RetErrorMsg = 'You must be a member of the ''sysadmin'' or ''db_owner'' role to execute this procedure!'
		goto Err_Handler
	end

	-- check if published for merge
	if not exists(select * from sysobjects where name = 'sysmergepublications')
	begin
		select @RetErrorMsg = 'The current database(' + db_name() + ') is not published for merge replication!'	
		goto Err_Handler
	end
	
	-- check to see if publication exists
	if not exists(select * from sysmergepublications where publisher_db = db_name() and name = @publication) 
	begin
		select @RetErrorMsg = 'The specified merge publication(' + @publication +') could not be found!'
		goto Err_Handler
	end

	/* 
	 * create a number of temp tables to house metadata for report generation
	 */
	if object_id('tempdb..##tbl_article_column') is not null 
	begin
		drop table ##tbl_article_column
	end

	if object_id('tempdb..##tbl_existing_index') is not null 
	begin
		drop table ##tbl_existing_index
	end
	
	if object_id('tempdb..##tbl_index_suggestion') is not null 
	begin
		drop table ##tbl_index_suggestion
	end
	
	if object_id('tempdb..##tbl_uniquekey_suggestion') is not null 
	begin
		drop table ##tbl_uniquekey_suggestion
	end

	if object_id('tempdb..##tmp_valid_operators') is not null 
	begin
		drop table ##tmp_valid_operators
	end	

	-- create global table to house info on each column in each article table
	create table ##tbl_article_column
	(	
		TableColID	int 		identity(1,1),
		TableName 	sysname,
		ColumnName 	sysname,
		Cardinality	float 		null,
		CurrentRowCount	int 		null
	)
	
	-- table to house current index info
	create table ##tbl_existing_index
	(  	
		index_table	sysname 	null,
		index_name	sysname,
		description	varchar(210), 
		index_keys	varchar(2078)
	)

	-- table to map the filters with the index suggestions
	create table ##tbl_index_suggestion
	(
		FilterID	int,
		TableColID	int,
		IsJoinFilter	bit,
		IsIndexed	bit		null,
		index_table	sysname 	null,
		index_name	sysname 	null
	)
	
	-- table of filter id's for those that will NOT benefit from the join_uniquekey flag set to 1
	create table ##tbl_uniquekey_suggestion
	(
		FilterID		int,
		TableColID		int,
		ActualJoinUniqueKey	bit,
		ExpectedJoinUniqueKey	bit
	)

	-- create the table to house all valid operators
	create table ##tmp_valid_operators
	(
		Operator varchar(10)
	)

	-- populate the operators table
	insert into ##tmp_valid_operators(Operator) values('>')
	insert into ##tmp_valid_operators(Operator) values('<')
	insert into ##tmp_valid_operators(Operator) values('<>')
	insert into ##tmp_valid_operators(Operator) values('=')
	insert into ##tmp_valid_operators(Operator) values('!=')
	insert into ##tmp_valid_operators(Operator) values('!>')
	insert into ##tmp_valid_operators(Operator) values('!<')
	insert into ##tmp_valid_operators(Operator) values(')')
	insert into ##tmp_valid_operators(Operator) values('(')
	
	-- populate the table which will house the data for each column in every article
	-- though not all columns are involved in filtering, we need all of the columns in our
	-- table so that we can detect the columns in the filter. 
	insert into ##tbl_article_column 
			(
				TableName,
				ColumnName
			) select quotename(object_name(ma.objid)), 
				sc.name
			from sysmergepublications mp,
				sysobjects so,
				sysmergearticles ma,
				syscolumns sc 
			where mp.publisher_db = db_name() 
				and mp.name = @publication
				and mp.pubid = ma.pubid
				and ma.objid = sc.id
				and ma.objid = so.id
				and so.type = 'U'
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured during inserts for ##tbl_article_column!'
		goto Err_Handler
	end

	/*
	 * Gather data for SubsetFilters
	 */

	-- declare cursor to step through articles and make sure to 
	-- retrieve the actual table names and not the article names
	-- here we will go through all of the articles even if subsetfilter
	-- is null. We go through all of them so that we can get the info
	-- on all current indexes for each article
	declare filter_cursor cursor fast_forward for
		select 	ma.nickname,				-- Article nickname but is used as the Filter ID
			quotename(object_name(ma.objid)), 	-- Article Table Name
			ma.subset_filterclause			-- Actual Filter Clause
		from sysmergepublications mp,
			sysobjects so,
			sysmergearticles ma
		where mp.publisher_db = db_name() 
			and mp.name = @publication
			and mp.pubid = ma.pubid
			and ma.objid = so.id
			and so.type = 'U'
	select @FilterCursorState = 1

	open filter_cursor
	select @FilterCursorState = 2
	
	fetch next from filter_cursor into @FilterID, @TableName, @Filter
	while @@fetch_status = 0
	begin
		-- only attempt to suggest indexes for subsetfilters that are not null or empty
		if @Filter is not null and rtrim(ltrim(@Filter)) <> ''
		begin
			exec @RetCode = proc_help_suggest_index @TableName, @FilterID, @Filter, 0
			if @RetCode <> 0 or @@error <> 0
			begin
				select @RetErrorMsg = 'Failed to generate index suggestions: subsetfilter ' + str(@FilterID) +  '!'
				goto Err_Handler
			end
		end
		
		-- get current indexes for the Article
		-- we perform this for each article as we will need the info to detect whether indexes exist
		-- for the article already. We need this information for each article as the filters can contain
		-- a join with any article in the publication.
		insert into ##tbl_existing_index (index_name, description, index_keys) exec sp_helpindex @TableName
		update ##tbl_existing_index set index_table = @TableName where index_table is null

		fetch next from filter_cursor into @FilterID, @TableName, @Filter			
	end
	
	close filter_cursor
	select @FilterCursorState = 3

	deallocate filter_cursor
	select @FilterCursorState = 0
	
	/*
	 * Gather data for JoinFilters
	 */

	-- declare cursor to step through article filters and make sure to 
	-- retrieve the actual table names and not the article names
	declare filter_cursor cursor fast_forward for
		select 	mf.join_filterid,			-- Filter ID
			quotename(object_name(ma1.objid)),	-- Article Table Name
			quotename(object_name(ma2.objid)),	-- JoinArticle Table Name
			mf.join_filterclause,			-- Actual Filter Clause
			mf.join_unique_key			-- joinuniquekey value
		from sysmergepublications mp,			
			sysobjects so1,				
			sysobjects so2,				
			sysmergearticles ma1,			
			sysmergearticles ma2,
			sysmergesubsetfilters mf
		where mp.publisher_db = db_name() 
			and mp.name = @publication
			and mp.pubid = ma1.pubid
			and mp.pubid = ma2.pubid
			and mp.pubid = mf.pubid
			and ma1.objid = so1.id
			and ma2.objid = so2.id
			and so1.type = 'U'			-- make sure they are user tables articles
			and so2.type = 'U'			-- make sure they are user tables articles
			and mf.art_nickname = ma1.nickname 	
			and mf.join_nickname = ma2.nickname
	select @FilterCursorState = 1

	open filter_cursor
	select @FilterCursorState = 2

	fetch next from filter_cursor into @FilterID, @TableName, @JoinTableName, @Filter, @JoinUniqueKey
	while @@fetch_status = 0
	begin			
		-- for each filter suggest some indexes
		exec @RetCode = proc_help_suggest_index @TableName, @FilterID, @Filter, 1
		if @RetCode <> 0 or @@error <> 0
		begin
			select @RetErrorMsg = 'Failed to generate index suggestions: join filter ' + str(@FilterID) +  '!'
			goto Err_Handler
		end	
		
		-- if only 1 index suggestion exists for the join article we will determine if join unique key value should 
		-- be off for this article else we set everything to null
		if (select count(*) from ##tbl_index_suggestion tis, ##tbl_article_column tac where tis.FilterID = @FilterID and tis.TableColID = tac.TableColID and tac.TableName = @JoinTableName) = 1
		begin
			select @JoinCol = tac.ColumnName,
				@TableColID = tac.TableColID
			from ##tbl_index_suggestion tis, 
				##tbl_article_column tac 
			where tis.FilterID = @FilterID 
				and tis.TableColID = tac.TableColID
				and tac.TableName = @JoinTableName	-- need this quailification since index
									-- sugestion will always have 2 per filter
									-- this distinguishes the Join article suggestion
									-- from Main article suggestion
			
			select @Query = 'declare @ExpectedJoinUniqueKey bit select @ExpectedJoinUniqueKey = 1 if exists(select count(*) from ' + @JoinTableName + ' group by  ' + @JoinCol + ' having count(*) > 1) begin select @ExpectedJoinUniqueKey = 0 end insert into ##tbl_uniquekey_suggestion (FilterID, TableColID, ActualJoinUniqueKey, ExpectedJoinUniqueKey) values(' + cast(@FilterID as varchar) + ', ' +  cast(@TableColID as varchar) + ', ' + cast(@JoinUniqueKey as varchar) + ', @ExpectedJoinUniqueKey)'
		end
		else
		begin
			select @Query = 'insert into ##tbl_uniquekey_suggestion (FilterID, TableColID, ActualJoinUniqueKey, ExpectedJoinUniqueKey) values(' + cast(@FilterID as varchar) + ', null, ' + cast(@JoinUniqueKey as varchar) + ', null)'
		end

		exec(@Query)
		if @@error <> 0
		begin
			select @RetErrorMsg = 'Failed to perform insert for join unique key value: filter ' + cast(@FilterID as varchar) +  '!'
			goto Err_Handler
		end

		fetch next from filter_cursor into @FilterID, @TableName, @JoinTableName, @Filter, @JoinUniqueKey
	end

	close filter_cursor
	select @FilterCursorState = 3

	deallocate filter_cursor
	select @FilterCursorState = 0
	
	/* Perform results processing before continueing to generate the global report tables */ 

	-- cleanup the ##tbl_existing_index
	-- remove all hypothetical indexes and statistics from the table
	delete ##tbl_existing_index 
		where description like '%statistics%' 
			or description like '%hypothetical%'

	-- check to see if an index exists for a column involved in filtering 
	update ##tbl_index_suggestion
	set IsIndexed = 1,
		index_table = tei.index_table,
		index_name = tei.index_name
	from ##tbl_existing_index tei,
		##tbl_index_suggestion tis, 
		##tbl_article_column tac
	where tei.index_table = tac.TableName
		and tis.TableColID = tac.TableColID
		and ' ' + tei.index_keys + ',' like '% ' + tac.ColumnName + ',%'
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured searching for existing indexes on columns involved in filters!'
		goto Err_Handler
	end

	-- generate cardinality value for all columns involved in filtering and not indexed
	-- generate row count for all columns requardless of wether or not they are indexed
	declare filter_cursor cursor fast_forward for
		select distinct tac.TableName,
			tac.ColumnName,
			tis.IsIndexed
		from ##tbl_index_suggestion tis, 
			##tbl_article_column tac
		where tis.TableColID = tac.TableColID
	select @FilterCursorState = 1

	open filter_cursor
	select @FilterCursorState = 2

	fetch next from filter_cursor into @TableName, @ArtCol, @IsIndexed
	while @@fetch_status = 0
	begin	
		-- only attempt to get cardinality if not rowquid and it is not index. 
		-- if it is a rowguid set cardinality to 1.0 and get rowcount only
		-- if it is already indexed set cardinality to 1.0 and get rowcount only
		if @IsIndexed = 0 and not exists(select * from syscolumns sc, systypes st where sc.id = object_id(@TableName) and sc.name = @ArtCol and st.name = 'uniqueidentifier' and sc.xtype = st.xtype)
		begin 
			select @Query = 'declare @dividend real declare @divisor real select @dividend = convert(real, count(distinct ' + @ArtCol + ')), @divisor = case count(*) when 0 then 1 else count(*) end from ' + @TableName + ' update ##tbl_article_column set Cardinality = (@dividend/@divisor), CurrentRowCount = @divisor where TableName = ''' + @TableName + ''' and ColumnName = ''' + @ArtCol + ''''
		end
		else
		begin
			select @Query = 'declare @CurRowCount int select @CurRowCount = count(*) from ' + @TableName + ' update ##tbl_article_column set Cardinality = 1.0, CurrentRowCount = @CurRowCount where TableName = ''' + @TableName + ''' and ColumnName = ''' + @ArtCol + ''''
		end
		
		exec (@Query)
		if @@error <> 0
		begin
			select @RetErrorMsg = 'Failed to generate cardinality value!'
			goto Err_Handler
		end

		fetch next from filter_cursor into @TableName, @ArtCol, @IsIndexed
	end
	
	close filter_cursor
	select @FilterCursorState = 3

	deallocate filter_cursor
	select @FilterCursorState = 0

	/* 
	 * create report tables
	 */
	
	if object_id('tempdb..##tbl_uniquekey_report') is not null 
	begin
		drop table ##tbl_uniquekey_report
	end

	if object_id('tempdb..##tbl_index_report') is not null 
	begin
		drop table ##tbl_index_report
	end

	if object_id('tempdb..##tbl_existing_index_report') is not null 
	begin
		drop table ##tbl_existing_index_report
	end
	
	if object_id('tempdb..##tbl_filter_nesting_report') is not null
	begin
		drop table ##tbl_filter_nesting_report
	end
	
	create table ##tbl_uniquekey_report
	(
		FilterID		int,
		FilterName		sysname,	
		FilterExpression	varchar(1000),	
		ActualJoinUniqueKey	bit,
		ExpectedJoinUniqueKey	bit,
		JoinTable		sysname null,
		JoinColumn		sysname null,
		CurrentRowCount		int
	)
	
	create table ##tbl_index_report
	(
		FilterID		int,
		FilterName 		sysname,
		FilterExpression	varchar(1000),
		IsJoinFilter		bit,
		TableName 		sysname,
		ColumnName 		sysname,
		Cardinality		float null,
		CurrentRowCount		int null
	)	

	create table ##tbl_existing_index_report
	(
		FilterID		int,
		FilterName 		sysname,
		FilterExpression	varchar(1000),
		IsJoinFilter		bit,
		index_name		sysname,
		description		varchar(210),
		index_table		sysname null,
		index_keys		varchar(2078),
		TableName 		sysname,
		ColumnName 		sysname
	)

	-- create table to house information on filter nesting levels
	create table ##tbl_filter_nesting_report
	(
		Article		sysname		null,
		TableName	sysname,
		NestLevel	int,	
		NickName	int,
		FilterID	int 		null,
		Filter		varchar(1000) 	null,
		IsJoinFilter	bit 		null
	)

	/* 
	 * load report for join unique key values 
	 */
	insert into ##tbl_uniquekey_report 
	(
		FilterID,
		FilterName,
		FilterExpression,
		ActualJoinUniqueKey,
		ExpectedJoinUniqueKey,
		JoinTable,
		JoinColumn,
		CurrentRowCount	
	) select distinct tus.FilterID,
			mf.filtername,
			mf.join_filterclause,
			tus.ActualJoinUniqueKey,
			tus.ExpectedJoinUniqueKey,
			case when tus.TableColID is null 
				then null 
				else quotename(tac.TableName)
			end,
			case when tus.TableColID is null 
				then null 
				else tac.ColumnName
			end,
			case when tus.TableColID is null 
				then null 
				else tac.CurrentRowCount 
			end
		from sysmergepublications mp,
			sysmergesubsetfilters mf,
			##tbl_article_column tac,
			##tbl_uniquekey_suggestion tus
		where mp.publisher_db = db_name() 
			and mp.name = @publication
			and mp.pubid = mf.pubid
			and tus.FilterID = mf.join_filterid
			and (tus.TableColID = tac.TableColID
				or tus.TableColID is null)
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured loading join unique key report!'
		goto Err_Handler
	end

	/* 
	 * load report indexes that are missing 
	 */
	
	-- load index suggestions for subsetfilters
	insert into ##tbl_index_report 
	(
		FilterID,
		FilterName,
		FilterExpression,
		IsJoinFilter,
		TableName,
		ColumnName,
		Cardinality,
		CurrentRowCount
	) select tis.FilterID, 
			quotename(ma.name),
			ma.subset_filterclause, 
			tis.IsJoinFilter, 
			quotename(tac.TableName),
			tac.ColumnName,
			tac.Cardinality,
			tac.CurrentRowCount
		from sysmergepublications mp,
			sysmergearticles ma,
			##tbl_article_column tac, 
			##tbl_index_suggestion tis
		where mp.publisher_db = db_name() 
			and mp.name = @publication
			and mp.pubid = ma.pubid
			and tis.TableColID = tac.TableColID
			and tis.FilterID = ma.nickname
			and tis.IsIndexed = 0
			and tis.IsJoinFilter = 0
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured loading index report for subsetfilters!'
		goto Err_Handler
	end

	-- load index suggestions for join filters
	insert into ##tbl_index_report 
	(
		FilterID,
		FilterName,
		FilterExpression,
		IsJoinFilter,
		TableName,
		ColumnName,
		Cardinality,
		CurrentRowCount
	) select tis.FilterID,
			mf.filtername,
			mf.join_filterclause,
			tis.IsJoinFilter, 
			quotename(tac.TableName),
			tac.ColumnName,
			tac.Cardinality,
			tac.CurrentRowCount
		from sysmergepublications mp,
			sysmergesubsetfilters mf,
			##tbl_article_column tac, 
			##tbl_index_suggestion tis
		where mp.publisher_db = db_name() 
			and mp.name = @publication
			and mp.pubid = mf.pubid
			and tis.TableColID = tac.TableColID
			and tis.FilterID = mf.join_filterid
			and tis.IsIndexed = 0
			and tis.IsJoinFilter = 1
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured loading index report for join filters!'
		goto Err_Handler
	end

	/* 
	 * load report for existing indexes on filtered columns
	 */
	-- load index suggestions for subsetfilters
	insert into ##tbl_existing_index_report 
	(
		FilterID,
		FilterName,
		FilterExpression,
		IsJoinFilter,
		index_name,
		description,
		index_table,
		index_keys,
		TableName,
		ColumnName
	) select tis.FilterID,
			ma.name,
			ma.subset_filterclause,
			tis.IsJoinFilter,
			tei.index_name,
			tei.description,
			tei.index_table,
			tei.index_keys,
			tac.TableName,
			tac.ColumnName
		from sysmergepublications mp,
			sysmergearticles ma,
			##tbl_existing_index tei,
			##tbl_index_suggestion tis, 
			##tbl_article_column tac
		where mp.publisher_db = db_name() 
			and mp.name = @publication
			and mp.pubid = ma.pubid
			and tis.TableColID = tac.TableColID
			and tis.index_name = tei.index_name
			and tis.index_table = tei.index_table
			and tis.FilterID = ma.nickname
			and tis.IsIndexed = 1
			and tis.IsJoinFilter = 0
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured loading existing index report for subsetfilters!'
		goto Err_Handler
	end
	
	-- load index suggestions for join filters
	insert into ##tbl_existing_index_report 
	(
		FilterID,
		FilterName,
		FilterExpression,
		IsJoinFilter,
		index_name,
		description,
		index_table,
		index_keys,
		TableName,
		ColumnName
	) select tis.FilterID,
			mf.filtername,
			mf.join_filterclause,
			tis.IsJoinFilter,
			tei.index_name,
			tei.description,
			tei.index_table,
			tei.index_keys,
			quotename(tac.TableName),
			tac.ColumnName
		from sysmergepublications mp,
			sysmergesubsetfilters mf,
			##tbl_existing_index tei,
			##tbl_index_suggestion tis, 
			##tbl_article_column tac
		where mp.publisher_db = db_name() 
			and mp.name = @publication
			and mp.pubid = mf.pubid
			and tis.TableColID = tac.TableColID
			and tis.index_name = tei.index_name
			and tis.index_table = tei.index_table
			and tis.FilterID = mf.join_filterid
			and tis.IsIndexed = 1
			and tis.IsJoinFilter = 1
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured loading existing index report for join filters!'
		goto Err_Handler
	end

	/*
	 * load report for filter nesting levels
	 */
	
	-- set currentlevel and newlevel to 0
	select @CurrentLevel 		= 0
	select @NewLevel 		= 0

	-- populate nesting level table with the nicknames of each table article 
	-- that currently exists. here we should use a level of 0
	insert into ##tbl_filter_nesting_report
	(
		Article,
		TableName,
		NestLevel,
		NickName
	) select ma.name,
			quotename(object_name(ma.objid)),
			@NewLevel,
			ma.nickname
		from sysmergepublications mp,
			sysmergearticles ma,
			sysobjects so
		where mp.publisher_db = db_name() 
			and mp.name = @publication
			and mp.pubid = ma.pubid
			and ma.objid = so.id
			and so.type = 'U'		-- ensure that only table articles are being checked
	if @@error <> 0
	begin
		select @RetErrorMsg = 'An error occured loading level 0 of the filter nesting level report!'
		goto Err_Handler
	end
	
	-- loop through until we are done with all levels (note that @NewLevel is incremented within the loop)
	while exists(select * from ##tbl_filter_nesting_report where NestLevel = @NewLevel)
	begin
		-- set the current level and the new level
		select @CurrentLevel = @NewLevel
		select @NewLevel = @NewLevel + 1

		-- declare a cursor that looks for nicknames that are within the current level
		declare filter_level_cursor cursor fast_forward for
			select TableName,
				NickName
			from ##tbl_filter_nesting_report
			where NestLevel = @CurrentLevel
				and (IsJoinFilter is null
						or IsJoinFilter = 1)		-- we do not need to expand subsetfilters
		select @FilterCursorState = 1

		open filter_level_cursor
		select @FilterCursorState = 2
		
		fetch next from filter_level_cursor into @TableName, @NickName
		while @@fetch_status = 0
		begin
			-- insert info for articles participating in a merge join filter
			-- with an article = to nickname
			insert into ##tbl_filter_nesting_report
			(
				TableName,
				NestLevel,
				NickName,
				FilterID,
				Filter,
				IsJoinFilter
			) select @TableName, 
					@NewLevel,
					join_nickname, 
					join_filterid,
					join_filterclause,
					1 
				from sysmergepublications mp,
					sysmergesubsetfilters mf
				where mp.publisher_db = db_name() 
					and mp.name = @publication
					and mp.pubid = mf.pubid
					and mf.art_nickname = @NickName
			if @@error <> 0
			begin
				select @RetErrorMsg = 'An error occured loading levels of the filter nesting level report for join filters!'
				goto Err_Handler
			end
			
			-- insert info for articles  = to nickname containing a subset filter
			insert into ##tbl_filter_nesting_report
			(
				TableName,
				NestLevel,
				NickName,
				FilterID,
				Filter,
				IsJoinFilter
			) select @TableName, 
					@NewLevel,
					@NickName,
					nickname,
					subset_filterclause,
					0 
				from sysmergepublications mp,
					sysmergearticles ma
				where mp.publisher_db = db_name() 
					and mp.name = @publication
					and mp.pubid = ma.pubid
					and ma.nickname = @NickName
					and ma.subset_filterclause is not null		-- here we do not need to qualify for table articles via sysobjects 
											-- since only table articles can have a non-null subsetfilterclause
			if @@error <> 0
			begin
				select @RetErrorMsg = 'An error occured loading levels of the filter nesting level report for subsetfilters!'
				goto Err_Handler
			end

			fetch next from filter_level_cursor into @TableName, @NickName
		end
	
		close filter_level_cursor
		select @FilterCursorState = 3
	
		deallocate filter_level_cursor
		select @FilterCursorState = 0
	end

	/*
	 * Check to see if we want to run full report
	 */

	if @full_report = 1 
	begin
		exec @RetCode = proc_help_gen_full_report @publication	
		if @RetCode <> 0 or @@error <> 0
		begin
			select @RetErrorMsg = 'Failed on generating the full report!'
			goto Err_Handler
		end
		
		-- full report will delete the report tables for now.
		select @keep_report_tables = 0
	end
	
	-- only display report tables if we will delete them later and not full 
	-- report. if not (ie @keep_report_tables = 1) then we can assume that
	-- the user will be performing their own processing and will display/select
	-- the tables at a later time. tables will be removed in the cleanup 
	-- phase of this proc
	if @full_report = 0 and @keep_report_tables = 0
	begin
		select * 
		from ##tbl_uniquekey_report 
		order by FilterID
		
		select * 
		from ##tbl_index_report 
		order by FilterID
	
		select * 
		from ##tbl_existing_index_report 
		order by FilterID
	
		select * from ##tbl_filter_nesting_report
		order by TableName, NestLevel
	end

	-- done successfully so set the retcode to 0
	select @RetCode = 0
	goto CleanUp

Err_Handler:
	-- since failure ocured make sure to delete the report tables
	select @keep_report_tables = 0

	-- failure occured so set the retcode to 1
	select @RetCode = 1
	raiserror(@RetErrorMsg, 16, 1)	
CleanUp:
	-- only leave report tables if it is requested by user setting @keep_report_tables = 1
	if @keep_report_tables = 0
	begin
		if object_id('tempdb..##tbl_uniquekey_report') is not null 
		begin
			drop table ##tbl_uniquekey_report
		end
	
		if object_id('tempdb..##tbl_index_report') is not null 
		begin
			drop table ##tbl_index_report
		end
	
		if object_id('tempdb..##tbl_existing_index_report') is not null 
		begin
			drop table ##tbl_existing_index_report
		end
		
		if object_id('tempdb..##tbl_filter_nesting_report') is not null 
		begin
			drop table ##tbl_filter_nesting_report
		end
	end

	-- cleanup all of the temp tables used in the proc
	if object_id('tempdb..##tbl_article_column') is not null 
	begin
		drop table ##tbl_article_column
	end

	if object_id('tempdb..##tbl_existing_index') is not null 
	begin
		drop table ##tbl_existing_index
	end
	
	if object_id('tempdb..##tbl_index_suggestion') is not null 
	begin
		drop table ##tbl_index_suggestion
	end
	
	if object_id('tempdb..##tbl_uniquekey_suggestion') is not null 
	begin
		drop table ##tbl_uniquekey_suggestion
	end

	if object_id('tempdb..##tmp_valid_operators') is not null 
	begin
		drop table ##tmp_valid_operators
	end	
	
	-- close or deallocate cursors as necessary
	if @FilterCursorState = 2
	begin 
		close filter_cursor
	end

	if @FilterCursorState in (1, 2, 3)
	begin 
		deallocate filter_cursor
	end
Done:	
	return @RetCode
end
GO