Set NoCount On;

	Declare @ShowDetails Bit;
	Declare @IsAutoFixEnabled Bit;


	/*================================================================================================================================*/

	Set @ShowDetails = 1;
	Set @IsAutoFixEnabled = 0;
	
	/*================================================================================================================================*/


	-- Local Variables
	Declare @IssueNum Int;
	Declare @Description VarChar(Max);
	Declare @SqlTables VarChar(250);
	Declare @FixType VarChar(50);	-- Auto or Manual
	Declare @NumOfRecords Int;

	-- Create Results Table
	Declare @Results as Table (IssueNum Int Primary Key, SqlTables VarChar(250), [Description] VarChar(Max), FixType VarChar(50), NumOfRecords Int);

	-- Default / Initialize Variables
	Set @IssueNum = 0;
	Set @Description = 'n/a';
	Set @SqlTables = 'n/a';
	Set @FixType = 'Manual';
	Set @NumOfRecords = 0;



	/*======================================================================================================*/
	-- Case/Issue #1:	[Contents Table] - Records with space before Links after parenthesis
	/*======================================================================================================*/
	Set @IssueNum = 1;
	Set @Description = 'Records with space before Links after parenthesis';
	Set @SqlTables = 'Contents';
	Set @FixType = 'Auto';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	Select @NumOfRecords = Count(1) 
	From Contents c with (NoLock) Where c.[Text] like '%( \v%'; 

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');

		Select c.ContentID, c.[Text], c.Config, c.DTS, c.UserID, c.LastChanged From Contents c with (NoLock) Where c.[Text] like '%( \v%';
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue   
	---------------------------------------------------------------------------------------
	If (@IsAutoFixEnabled = 1 And @NumOfRecords > 0)
	Begin
		Update tblContents Set [Text] = Right([Text], Len([Text])-1) Where [Text] like '%( \v%';
	End


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);
	



	/*======================================================================================================*/
	-- Case/Issue #2:	[Contents Table] - Records with space before Links as starting text
	/*======================================================================================================*/
	Set @IssueNum = 2;
	Set @Description = 'Records with space before Links as starting text';
	Set @SqlTables = 'Contents';
	Set @FixType = 'Auto';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	Select @NumOfRecords = Count(1) 
	From Contents c with (NoLock) Where c.[Text] like ' \v%';	 

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');

		Select c.ContentID, c.[Text], c.Config, c.DTS, c.UserID, c.LastChanged From Contents c with (NoLock) Where c.[Text] like ' \v%';
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue   
	---------------------------------------------------------------------------------------
	If (@IsAutoFixEnabled = 1 And @NumOfRecords > 0)
	Begin
		Update tblContents Set [Text] = Right([Text], Len([Text])-1) Where [Text] like ' \v%';
	End


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);
	




	/*======================================================================================================*/
	-- Case/Issue #3:	[Contents Table] - Records with extra spaces before RO Links
	/*======================================================================================================*/
	Set @IssueNum = 3;
	Set @Description = 'Records with extra spaces before RO Links';
	Set @SqlTables = 'Contents';
	Set @FixType = 'Auto';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	--Select @NumOfRecords = Count(1) 
	--From Contents c with (NoLock) Where c.[Text] like '%  \v%';	

	Select @NumOfRecords = Count(1) 
	From Contents c with (NoLock) 
	Where c.[Text] like '%  \v%' And Not (c.[Text] like '%\ul  \v%' Or c.[Text] like '%\ulnone  \v%' Or c.[Text] like '%\up0  \v%' Or c.[Text] like '%\b0  \v%' Or c.[Text] like '%\i0  \v%');

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');

		--Select c.ContentID, c.[Text], c.Config, c.DTS, c.UserID, c.LastChanged From Contents c with (NoLock) Where c.[Text] like '%  \v%';

		Select c.ContentID, c.[Text], c.Config, c.DTS, c.UserID, c.LastChanged From Contents c with (NoLock) 
		Where c.[Text] like '%  \v%' And Not (c.[Text] like '%\ul  \v%' Or c.[Text] like '%\ulnone  \v%' Or c.[Text] like '%\up0  \v%' Or c.[Text] like '%\b0  \v%' Or c.[Text] like '%\i0  \v%');
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue   
	---------------------------------------------------------------------------------------
	If (@IsAutoFixEnabled = 1 And @NumOfRecords > 0)
	Begin
		--Update tblContents Set [Text] = Replace([Text], '  \v', ' \v') Where [Text] like '%  \v%';

		Update tblContents Set [Text] = Replace([Text], '  \v', ' \v') 
		Where [Text] like '%  \v%' And Not ([Text] like '%\ul  \v%' Or [Text] like '%\ulnone  \v%' Or [Text] like '%\up0  \v%' Or [Text] like '%\b0  \v%' Or [Text] like '%\i0  \v%');
	End


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);





	/*======================================================================================================*/
	-- Case/Issue #4:	[Grids Table] - Records with extra spaces before RO Links
	/*======================================================================================================*/
	Set @IssueNum = 4;
	Set @Description = 'Records with extra spaces before RO Links';
	Set @SqlTables = 'Grids';
	Set @FixType = 'Auto';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	Select @NumOfRecords = Count(1) 
	From Grids g with (NoLock) 
	Where Cast(g.[Data] as VarChar(Max)) like '%  \v%'
		And Not (Cast(g.[Data] as VarChar(Max)) like '%\ul  \v%' Or Cast(g.[Data] as VarChar(Max)) like '%\ulnone  \v%' 
			Or Cast(g.[Data] as VarChar(Max)) like '%\up0  \v%' Or Cast(g.[Data] as VarChar(Max)) like '%\b0  \v%' Or Cast(g.[Data] as VarChar(Max)) like '%\i0  \v%');

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');

		Select g.ContentID, g.Config, g.DTS, g.UserID, g.LastChanged, Cast(g.[Data] as VarChar(Max)) as 'XmlDataAsText', g.[Data] as 'XmlData' 
		From Grids g with (NoLock) 
		Where Cast(g.[Data] as VarChar(Max)) like '%  \v%'
			And Not (Cast(g.[Data] as VarChar(Max)) like '%\ul  \v%' Or Cast(g.[Data] as VarChar(Max)) like '%\ulnone  \v%' 
			Or Cast(g.[Data] as VarChar(Max)) like '%\up0  \v%' Or Cast(g.[Data] as VarChar(Max)) like '%\b0  \v%' Or Cast(g.[Data] as VarChar(Max)) like '%\i0  \v%');
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue   
	---------------------------------------------------------------------------------------
	If (@IsAutoFixEnabled = 1 And @NumOfRecords > 0)
	Begin

		Update tblGrids Set [Data] = Cast(Replace(Cast([Data] as VarChar(Max)), '  \v', ' \v') as xml)
		Where Cast([Data] as VarChar(Max)) like '%  \v%'
			And Not (Cast([Data] as VarChar(Max)) like '%\ul  \v%' Or Cast([Data] as VarChar(Max)) like '%\ulnone  \v%' 
			Or Cast([Data] as VarChar(Max)) like '%\up0  \v%' Or Cast([Data] as VarChar(Max)) like '%\b0  \v%' Or Cast([Data] as VarChar(Max)) like '%\i0  \v%');
	End


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);
	




	/*======================================================================================================*/
	-- Case/Issue #5:	[Contents Table] - Records with UnEscaped Hyphens
	/*======================================================================================================*/
	Set @IssueNum = 5;
	Set @Description = 'Records with UnEscaped Hyphens';
	Set @SqlTables = 'Contents';
	Set @FixType = 'Auto';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	Select @NumOfRecords = Count(1) 
	From Contents c with (NoLock) Where c.[Text] like '%-%';

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');

		Select c.ContentID, c.[Text], c.Config, c.DTS, c.UserID, c.LastChanged From Contents c with (NoLock) Where c.[Text] like '%-%';
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue   
	---------------------------------------------------------------------------------------
	If (@IsAutoFixEnabled = 1 And @NumOfRecords > 0)
	Begin
		Update tblContents Set [Text] = Replace([Text], '-', '\u8209?') Where [Text] like '%-%';
	End


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);




	/*======================================================================================================*/
	-- Case/Issue #6:	[Grid Table] - Records with Escaped Hyphens
	/*======================================================================================================*/
	Set @IssueNum = 6;
	Set @Description = 'Records with Escaped Hyphens';
	Set @SqlTables = 'Grids';
	Set @FixType = 'Auto';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	Select @NumOfRecords = Count(1) 
	From Grids g with (NoLock) Where Cast(g.[Data] as VarChar(Max)) like '%\u8209?%'; 

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');

		Select g.ContentID, g.Config, g.DTS, g.UserID, g.LastChanged, Cast(g.[Data] as VarChar(Max)) as 'XmlDataAsText', g.[Data] as 'XmlData' 
		From Grids g with (NoLock) Where Cast(g.[Data] as VarChar(Max)) like '%\u8209?%';
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue   
	---------------------------------------------------------------------------------------
	If (@IsAutoFixEnabled = 1 And @NumOfRecords > 0)
	Begin
		Update tblGrids Set [Data] = Cast(Replace(Cast([Data] as VarChar(Max)), '\u8209?', '-') as xml) Where Cast([Data] as VarChar(Max)) like '%\u8209?%';
	End


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);





	/*======================================================================================================*/
	-- Case/Issue #7:	[Contents Table] - Records with Double <START] tags
	/*======================================================================================================*/
	Set @IssueNum = 7;
	Set @Description = 'Records with Double <START] tags';
	Set @SqlTables = 'Contents';
	Set @FixType = 'Auto';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	Select @NumOfRecords = Count(1) 
	From Contents c with (NoLock) left outer join Grids g with (NoLock) on g.ContentID = c.ContentID Where g.ContentID is null And c.[Text] like '%<START]<START]%';

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');

		Select c.ContentID, c.[Text], c.Config, c.DTS, c.UserID, c.LastChanged 
		From Contents c with (NoLock) left outer join Grids g with (NoLock) on g.ContentID = c.ContentID 
		Where g.ContentID is null And c.[Text] like '%<START]<START]%';
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue   
	---------------------------------------------------------------------------------------
	If (@IsAutoFixEnabled = 1 And @NumOfRecords > 0)
	Begin
		Update tblContents  Set [Text] = Replace([Text], '<START]<START]', '<START]')  Where [Text] like '%<START]<START]%' And ContentID Not in (Select ContentID From Grids);
	End


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);




	/*======================================================================================================*/
	-- Case/Issue #8:	[Grids/Contents Table] - Records with Double <START] tags
	/*======================================================================================================*/
	Set @IssueNum = 8;
	Set @Description = 'Records with Double <START] tags';
	Set @SqlTables = 'Grids/Contents';
	Set @FixType = 'Auto';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	Select @NumOfRecords = Count(1) 
	From Contents c with (NoLock) inner join Grids g with (NoLock) on g.ContentID = c.ContentID 
	Where (c.[Text] like '%<START]<START]%' Or Cast(g.[Data] as VarChar(Max)) like '%&lt;START]&lt;START]%');
	
	--Select c.ContentID, c.[Text] as 'Text', Cast(g.[Data] as VarChar(Max)) as 'XmlDataAsText',  g.[Data] as 'XmlData'
	--From Contents c with (NoLock) inner join Grids g with (NoLock) on g.ContentID = c.ContentID 
	--Where (c.[Text] like '%<START]<START]%' Or Cast(g.[Data] as VarChar(Max)) like '%&lt;START]&lt;START]%');

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');
		
		Select c.ContentID, c.[Text], Cast(g.[Data] as VarChar(Max)) as 'XmlDataAsText',  g.[Data] as 'XmlData'
		From Contents c with (NoLock) inner join Grids g with (NoLock) on g.ContentID = c.ContentID 
		Where (c.[Text] like '%<START]<START]%' Or Cast(g.[Data] as VarChar(Max)) like '%&lt;START]&lt;START]%');
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue   
	---------------------------------------------------------------------------------------
	If (@IsAutoFixEnabled = 1 And @NumOfRecords > 0)
	Begin
		Update tblContents Set [Text] = Replace([Text], '<START]<START]', '<START]') Where [Text] like '%<START]<START]%'; 
		Update tblGrids Set [Data] = Cast(Replace(Cast([Data] as VarChar(Max)), '&lt;START]&lt;START]', '&lt;START]') as xml) Where Cast([Data] as VarChar(Max)) like '%&lt;START]&lt;START]%';
	End


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);




	/*======================================================================================================*/
	-- Case/Issue #9:	[Contents Table] - Records with Num of <START] / [END> tags don't match
	/*======================================================================================================*/
	Set @IssueNum = 9;
	Set @Description = 'Num of <START] / [END> tags don''t match';
	Set @SqlTables = 'Contents';
	Set @FixType = 'Manual';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	With zContents as 
	( 
		Select c.ContentID, c.[Text], dbo.vefn_NumOfOccurrences(c.[Text], '<START]') as 'StartCntText', dbo.vefn_NumOfOccurrences(c.[Text], '[END>') as 'EndCntText'
		From Contents c with (NoLock) left outer join Grids g with (NoLock) on g.ContentID = c.ContentID Where g.ContentID is null
	)
	Select @NumOfRecords = Count(1) 
	From zContents z Where z.StartCntText <> z.EndCntText;

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');
		
		With zContents as 
		( 
			Select c.ContentID, c.[Text], dbo.vefn_NumOfOccurrences(c.[Text], '<START]') as 'StartCntText', dbo.vefn_NumOfOccurrences(c.[Text], '[END>') as 'EndCntText'
			From Contents c with (NoLock) left outer join Grids g with (NoLock) on g.ContentID = c.ContentID Where g.ContentID is null
		)
		Select z.* 
		From zContents z Where z.StartCntText <> z.EndCntText;
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue  
	---------------------------------------------------------------------------------------
	-- Data needs to be reviewed / fixed Manually


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);




	/*======================================================================================================*/
	-- Case/Issue #10:	[Grids/Contents Table] - Records with Num of <START] / [END> tags don't match
	/*======================================================================================================*/
	Set @IssueNum = 10;
	Set @Description = 'Num of <START] / [END> tags don''t match';
	Set @SqlTables = 'Grids/Contents';
	Set @FixType = 'Manual';


	-- Get Record Count for the Case/Issue
	---------------------------------------------------------------------------------------
	With zContents as 
	( 
		Select c.ContentID, 
			dbo.vefn_NumOfOccurrences(c.[Text], '<START]') as 'StartCntText', dbo.vefn_NumOfOccurrences(c.[Text], '[END>') as 'EndCntText',
			dbo.vefn_NumOfOccurrences(Replace(Cast(g.[Data] as VarChar(Max)),'&lt;', '<'), '<START]') as 'StartCntXml', 
			dbo.vefn_NumOfOccurrences(Replace(Cast(g.[Data] as VarChar(Max)),'&gt;', '>'), '[END>') as 'EndCntXml',
			c.[Text], Cast(g.[Data] as VarChar(Max)) as 'XmlDataAsText',  g.[Data] as 'XmlData'
		From Contents c with (NoLock) inner join Grids g with (NoLock) on g.ContentID = c.ContentID 
	)
	Select @NumOfRecords = Count(1) 
	From zContents z Where (z.StartCntText <> z.EndCntText Or z.StartCntXml <> z.EndCntXml);

	-- If Any Bad Data Records Exist, Get Record Details for the specific Case/Issue
	---------------------------------------------------------------------------------------
	If (@ShowDetails = 1 And @NumOfRecords > 0)
	Begin
		Select Concat('Case/Issue #', @IssueNum, ':  [', @SqlTables, '] - ', @Description, '  (', @NumOfRecords, ' record(s))');
		
		With zContents as 
		( 
			Select c.ContentID, 
				dbo.vefn_NumOfOccurrences(c.[Text], '<START]') as 'StartCntText', dbo.vefn_NumOfOccurrences(c.[Text], '[END>') as 'EndCntText',
				dbo.vefn_NumOfOccurrences(Replace(Cast(g.[Data] as VarChar(Max)),'&lt;', '<'), '<START]') as 'StartCntXml', 
				dbo.vefn_NumOfOccurrences(Replace(Cast(g.[Data] as VarChar(Max)),'&gt;', '>'), '[END>') as 'EndCntXml',
				c.[Text], Cast(g.[Data] as VarChar(Max)) as 'XmlDataAsText',  g.[Data] as 'XmlData'
			From Contents c with (NoLock) inner join Grids g with (NoLock) on g.ContentID = c.ContentID 
		)
		Select z.*
		From zContents z Where (z.StartCntText <> z.EndCntText Or z.StartCntXml <> z.EndCntXml);
	End
		
	-- If Enabled and Any Bad Data Records Exist, Automatically fix the bad data records for the specific Case/Issue  
	---------------------------------------------------------------------------------------
	-- Data needs to be reviewed / fixed Manually


	-- Create Results Record for the specific Case/Issue 
	---------------------------------------------------------------------------------------
	Insert Into @Results (IssueNum, SqlTables, [Description], FixType, NumOfRecords) 
	Values (@IssueNum, @SqlTables, @Description,  @FixType, @NumOfRecords);





	/*================================================================================================================================*/
	-- Return analysis/results for all of the bad data Case/Issue(s)
	/*================================================================================================================================*/
	Select IssueNum as 'Case #', SqlTables as 'SQL Tables', [Description] as 'Description', FixType as 'Type of Fix', NumOfRecords as '# of Records'
	From @Results  Order By IssueNum Asc;