---------------------- 
-- $DateTime: 2016/09/26 14:40:26 $ 
---------------------- 


DECLARE @db_name_check VARCHAR(64)
SELECT @db_name_check = (SELECT db_name())
IF @db_name_check NOT LIKE '%Audit%'
PRINT 'The database selected does not contain the word Audit in its name.  Please ensure the desired database has been selected.  
If this is the desired database, the modifications can be made by commenting out the SET NOEXEC ON line and executing the batch once again.'
IF @db_name_check NOT LIKE '%Audit%'
SET NOEXEC ON


-- Add Sp_helpindex2
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_helpindex2]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE sp_helpindex2 
GO

create procedure sp_helpindex2
	@objname nvarchar(776)		-- the table to check for indexes
 AS
--{5.7.2.96}

---------------------- 
-- $Change: 186435 $ 
-- $DateTime: 2012/07/31 11:40:26 $ 
-- $Author: builduser $ 
---------------------- 

set nocount on
set transaction isolation level read uncommitted

/*****************************************************************************************************
 sp_helpindex2 with included columns and other index info

5.7.2.38	06/10/2011	RL 	New Procedure to replace sp_helpindex.  This version will show included columns
5.7.2.45	01/11/2012	DC 	Removed call to sys.sp_MS_marksystemobject
5.7.2.96	06/04/2012	RL	Modified view of column order
******************************************************************************************************/

	set nocount on

	declare @objid int,			-- the object id of the table
			@indid smallint,	-- the index id of an index
			@groupid int,  		-- the filegroup id of an index
			@indname sysname,
			@groupname sysname,
			@status int,
			@keys nvarchar(2126),	--Length (16*max_identifierLength)+(15*2)+(16*3)
			@inc_columns	nvarchar(max),
			@inc_Count		smallint,
			@loop_inc_Count		smallint,
			@dbname	sysname,
			@ignore_dup_key	bit,
			@is_unique		bit,
			@is_hypothetical	bit,
			@is_primary_key	bit,
			@is_unique_key 	bit,
			@auto_created	bit,
			@no_recompute	bit

	-- 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)
	if @objid is NULL
	begin
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
	end

	-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
	declare ms_crs_ind cursor local static for
		select i.index_id, i.data_space_id, i.name,
			i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint,
			s.auto_created, s.no_recompute
		from sys.indexes i join sys.stats s
			on i.object_id = s.object_id and i.index_id = s.stats_id
		where i.object_id = @objid
	open ms_crs_ind
	fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @auto_created, @no_recompute

	-- IF NO INDEX, QUIT
	if @@fetch_status < 0
	begin
		deallocate ms_crs_ind
		raiserror(15472,-1,-1,@objname) -- Object does not have any indexes.
		return (0)
	end

	-- create temp tables
	CREATE TABLE #spindtab
	(
		index_name			sysname	collate database_default NOT NULL,
		index_id			int,
		ignore_dup_key		bit,
		is_unique			bit,
		is_hypothetical		bit,
		is_primary_key		bit,
		is_unique_key		bit,
		auto_created		bit,
		no_recompute		bit,
		groupname			sysname collate database_default NULL,
		index_keys			nvarchar(2126)	collate database_default NOT NULL, -- see @keys above for length descr
		inc_Count			smallint,
		inc_columns			nvarchar(max)
	)

	CREATE TABLE #IncludedColumns
	(	RowNumber	smallint,
		[Name]	nvarchar(128)
	)

	-- Now check out each index, figure out its type and 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.
		declare @i int, @thiskey nvarchar(131) -- 128+3

		select @keys = index_col(@objname, @indid, 1), @i = 2
		if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
			select @keys = @keys  + '(-)'

		select @thiskey = index_col(@objname, @indid, @i)
		if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
			select @thiskey = @thiskey + '(-)'

		while (@thiskey is not null )
		begin
			select @keys = @keys + ', ' + @thiskey, @i = @i + 1
			select @thiskey = index_col(@objname, @indid, @i)
			if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
				select @thiskey = @thiskey + '(-)'
		end

		-- Second, we'll figure out what the included columns are.
		SELECT @inc_Count = count(*)
		FROM
		sys.tables AS tbl
			INNER JOIN sys.indexes AS i 
				ON (i.index_id > 0 
					and i.is_hypothetical = 0) 
					AND (i.object_id=tbl.object_id)
			INNER JOIN sys.index_columns AS ic 
				ON (ic.column_id > 0 
					and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
					AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
			INNER JOIN sys.columns AS clmns 
				ON clmns.object_id = ic.object_id 
				and clmns.column_id = ic.column_id
		WHERE ic.is_included_column = 1
			and (i.index_id = @indid)
			and (tbl.object_id = @objid) 

		SET @inc_Columns = NULL

		IF @inc_Count > 0
		BEGIN
			DELETE FROM #IncludedColumns
			INSERT #IncludedColumns
				SELECT ROW_NUMBER() OVER (ORDER BY ic.index_column_id) 
				, clmns.name 
			FROM
			sys.tables AS tbl
			INNER JOIN sys.indexes AS si 
				ON (si.index_id > 0 
					and si.is_hypothetical = 0) 
					AND (si.object_id=tbl.object_id)
			INNER JOIN sys.index_columns AS ic 
				ON (ic.column_id > 0 
					and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
					AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
			INNER JOIN sys.columns AS clmns 
				ON clmns.object_id = ic.object_id 
				and clmns.column_id = ic.column_id
			WHERE ic.is_included_column = 1 and
				(si.index_id = @indid) and 
				(tbl.object_id= @objid)
			ORDER BY 1
	
			SELECT @inc_columns = [Name] 
				FROM #IncludedColumns 
				WHERE RowNumber = 1
			
			SET @loop_inc_Count = 1

			WHILE @loop_inc_Count < @inc_Count
			BEGIN
				SELECT @inc_columns = @inc_columns + ', ' + [Name] 
					FROM #IncludedColumns WHERE RowNumber = @loop_inc_Count + 1
				SET @loop_inc_Count = @loop_inc_Count + 1
			END
		END
	
		select @groupname = null
		select @groupname = name from sys.data_spaces where data_space_id = @groupid

		-- INSERT ROW FOR INDEX
		insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys, @inc_Count, @inc_columns)

		-- Next index
		fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
			@is_primary_key, @is_unique_key, @auto_created, @no_recompute
	end
	deallocate ms_crs_ind

	-- DISPLAY THE RESULTS
	select
		'index_name' = index_name,
		'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
				case when index_id = 1 then 'clustered' else 'nonclustered' end
				+ case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end
				+ case when is_unique <>0 then ', unique' else '' end
				+ case when is_hypothetical <>0 then ', hypothetical' else '' end
				+ case when is_primary_key <>0 then ', primary key' else '' end
				+ case when is_unique_key <>0 then ', unique key' else '' end
				+ case when auto_created <>0 then ', auto create' else '' end
				+ case when no_recompute <>0 then ', stats no recompute' else '' end
				+ ' located on ' + groupname),
		'index_keys' = index_keys,
		--'num_included_columns' = inc_Count,
		'included_columns' = inc_columns
	from #spindtab
	order by index_name

	return (0) -- sp_helpindex2
GO


--Add Ng_index_maint

IF OBJECT_ID('ng_index_maint' , 'P')IS NOT NULL
    BEGIN
        DROP PROCEDURE ng_index_maint
    END;
GO

CREATE PROCEDURE ng_index_maint 
    (
	   @pi_table_name varchar(128),
	   @pi_index_name varchar(128),
	   @pi_index_columns varchar(860),
	   @pi_included_columns varchar(1000),
	   @pi_primary_ind char(1),
	   @pi_clustered_ind char(1),
	   @pi_unique_ind char(1),
	   @pi_ng_system_ind char(1) = 'N',
	   @pi_fulltext_ind char(1) = 'N',
	   @pi_fill_factor int = 90,
	   @pi_print_ind char(1) = 'N',
	   @pi_build_offline_ind char(1) = 'N',
	   @pi_drop_index char(1) = 'N'
    )

AS
--{5.9.0.5}
----------------------
-- $Change: 413689 $
-- $DateTime: 2016/08/26 13:41:45 $
-- $Author: kwebb $
----------------------
/*****************************************************************************************************  
This stored procedure adds indexes to table.  If there is a simarly named one it will rename it  
5.8.2.38	07/15/2014	dw/kmw			Initial creation
5.8.2.47	12/30/2014	SSC				Added @offline_flag to parse the included columns for LOBs and set online=off
5.8.2.49	01/28/2015	SSC				Added condition within case statement to cover when an index already exists on the right table but both index columns and included columns do not match the columns passed in
5.8.3.6		3/10/2015	KMW				Added @pi_build_offline_ind  to set online=off for an index even if it can be built online
5.8.3.17	5/20/2015	KMW				Fixes found in KBM - moved insert into ng_indexes to the end
5.8.3.54	11/10/2015	JF	QC 146248	Based on the database version and edition all indexes are now built/rebuilt online where appropriate.
5.8.3.69	03/03/2016	SSC	QC 151972	Added @pi_drop_index to allow ng_index_maint to drop index only
5.8.3.69	03/10/2016	PCW	QC 151972	Removed unnecessary code, changed logic for drop index only, modified handling of 'NULL' in included column list
5.9.0.5		08/26/2016	KMW	QC 159427		Added code to check for an existing index before renaming.
******************************************************************************************************/ 
BEGIN

SET NOCOUNT ON;

DECLARE @table_name varchar(128),
	   @index_name varchar(128),
	   @index_columns varchar(860),
	   @included_columns varchar(1000),
	   @strip_index_columns varchar(860),
	   @strip_included_columns varchar(1000),
	   @order INT,
	   @orderStop INT,
	   @group_name varchar(24),
	   @unique_ind int,
	   @clustered_ind int,
	   @primary_ind int,
	   @sql varchar(4000),
	   @sql1 varchar(4000),
	   @sql_edition nvarchar(128),
	   @sql_vers varchar(128),
	   @sql_version integer, 
	   @ent_flag char(1),
	   @LOBonline2012Ent char(1),
	   @LOBonline2008R2Ent char(1),
	   @wrong_tb_name varchar(128),
	   @wrong_inx_name varchar(128),
   	   @index_type char(5) = 'INDEX',
	   @wrong_si_clust_ind int,
	   --@pi_new_variable VARCHAR(1000),
	   @query NVARCHAR(max),
	   @offline_flag CHAR(1) = 'N',
	   @dropsql varchar (4000),
	   @drop_index char(1) = 'N'

SET @drop_index = @pi_drop_index

SET @index_columns = replace(@pi_index_columns,',',', ')
SET @index_columns = replace(@index_columns,'  ',' ');

SET @included_columns = @pi_included_columns
IF @included_columns = 'NULL'
	BEGIN
		SET @included_columns = ''
	END
ELSE
	BEGIN
		SET @included_columns = replace(@pi_included_columns,',',', ')
		SET @included_columns = replace(@included_columns,'  ',' ')
	END

SET @group_name = CASE @pi_clustered_ind
				WHEN 'Y' THEN 'NEXTGEN_CORE'
				ELSE 'NEXTGEN_INDEX_1'
			   END

SET @unique_ind = CASE @pi_unique_ind
				WHEN 'Y' THEN 1
				ELSE 0
			   END

SET @clustered_ind = CASE @pi_clustered_ind
				    WHEN 'Y' THEN 1
				    ELSE 2
				 END

SET @primary_ind = CASE @pi_primary_ind
				 WHEN 'Y' THEN 1
				 ELSE 0
			    END

SELECT @table_name = REPLACE(@pi_table_name, ' ', ''),
	   @index_name = REPLACE(@pi_index_name, ' ', ''),
	   @sql_edition = CONVERT(nvarchar(128),(SELECT SERVERPROPERTY('editionid'))),
	   @sql_vers = CONVERT(varchar(128) , SERVERPROPERTY('productversion')),
	   @sql_version = SUBSTRING(@sql_vers , 1 , CHARINDEX('.' , @sql_vers) - 1)

SELECT @ent_flag = 
    CASE @sql_edition
	   WHEN  '-2117995310' THEN 'Y' --Developer Edition
	   WHEN  '1804890536'  THEN 'Y' --Enterprise Edition
	   WHEN  '1872460670'  THEN 'Y' --Enterprise Edition: Core-based Licensing
	   --WHEN  '610778273'  THEN 'Y' --Enterprise Evaluation
	   ELSE  'N'
    END

IF @sql_version < 11 AND @ent_flag = 'N' --2008 R2 and below NON-Enterprise Edition
	SELECT @LOBonline2008R2Ent = 'N'
ELSE IF @sql_version < 11 AND @ent_flag = 'Y' --2008 R2 and below Enterprise Edition
	SELECT @LOBonline2008R2Ent = 'Y'
ELSE IF @sql_version >= 11 AND @ent_flag = 'N' --2012 and above NON-Enterprise Edition
	SELECT @LOBonline2012Ent = 'N'
ELSE IF @sql_version >= 11 AND @ent_flag = 'Y' --2012 and above Enterprise Edition
	SELECT @LOBonline2012Ent = 'Y'
    
--SELECT
--	@pi_included_columns = REPLACE(@pi_included_columns , ' ' , ''), --to get rid of extra spaces in the column’s name
--	@pi_new_variable= '''' + REPLACE(@pi_included_columns , ',' , ''',''') + '''' --to get ‘’ around each column 

--Check to see if we are only dropping an index
IF @drop_index = 'Y'
	BEGIN
		--Drop index if @pi_drop_index = 'Y'	
		SET @dropsql = 
		'DROP INDEX '+@table_name+'.'+@index_name
		EXEC (@dropsql)

		--Drop index from ng_indexes if @pi_drop_index = 'Y'
		DELETE FROM ng_indexes WHERE index_name = @index_name

	END
ELSE
	BEGIN
		SELECT 
			@strip_index_columns = REPLACE(@index_columns,' ',''), --prep string for parsing below
			@strip_included_columns = REPLACE(@included_columns,' ','') --prep string for parsing below

		--Strip out key and included columns and insert into temp table to check for LOBs
		IF @strip_index_columns IS NOT NULL
			BEGIN
				DECLARE @tblWords TABLE(
										[Order] INT IDENTITY (1,1), 
										Word VARCHAR(20)
									   )
				DECLARE @Word VARCHAR(20), 
						@StartPos INT, 
						@Length INT
            
				WHILE LEN(@strip_index_columns) > 0
					BEGIN
						SET @StartPos = CHARINDEX(',', @strip_index_columns)
						IF @StartPos < 0 
							SET @StartPos = 0
						SET @Length = LEN(@strip_index_columns) - @StartPos - 1
						IF @Length < 0 
							SET @Length = 0
						IF @StartPos > 0
							BEGIN
								SET @Word = SUBSTRING(@strip_index_columns, 1, @StartPos - 1)
								SET @strip_index_columns = SUBSTRING(@strip_index_columns, @StartPos + 1, LEN(@strip_index_columns) - @StartPos)
							END
						ELSE
							BEGIN
								SET @Word = @strip_index_columns
								SET @strip_index_columns = ''
						END
						INSERT @tblWords (Word) VALUES(@Word)
					END
			END

		IF @strip_included_columns IS NOT NULL
			BEGIN          
				WHILE LEN(@strip_included_columns) > 0
					BEGIN
						SET @StartPos = CHARINDEX(',', @strip_included_columns)
						IF @StartPos < 0 
							SET @StartPos = 0
						SET @Length = LEN(@strip_included_columns) - @StartPos - 1
						IF @Length < 0 
							SET @Length = 0
						IF @StartPos > 0
							BEGIN
								SET @Word = SUBSTRING(@strip_included_columns, 1, @StartPos - 1)
								SET @strip_included_columns = SUBSTRING(@strip_included_columns, @StartPos + 1, LEN(@strip_included_columns) - @StartPos)
							END
						ELSE
							BEGIN
								SET @Word = @strip_included_columns
								SET @strip_included_columns = ''
						END
						INSERT @tblWords (Word) VALUES(@Word)
					END
			END

		--Set @offline_flag appropriately based on index type and SQL Server version. 
		--2012+ Enterprise Editions
		IF @LOBonline2012Ent = 'Y'
			BEGIN
				IF @clustered_ind = '1'
					BEGIN --check if TABLE has LOB columns
						IF EXISTS (SELECT 1 FROM sys.columns
									WHERE OBJECT_NAME(object_id) = @table_name
									AND system_type_id IN ('34','35','99'))
							BEGIN
								SET @offline_flag = 'Y'
							END
						ELSE
							BEGIN
								SET @offline_flag = 'N'
							END
					END
				ELSE IF @clustered_ind <> '1'
					BEGIN --check if INDEX to be created has LOB columns
						IF EXISTS (SELECT * FROM sys.columns
									WHERE OBJECT_NAME(object_id) = @table_name
									AND name in (select word from @tblWords)
									AND system_type_id IN ('34','35','99'))
							BEGIN
								SET @offline_flag = 'Y'
							END
						ELSE
							BEGIN
								SET @offline_flag = 'N'
							END
					END
			END
		--2008 R2 and below Enterprise Editions
		IF @LOBonline2008R2Ent = 'Y'
			BEGIN
				IF @clustered_ind = '1'
					BEGIN --check if TABLE has LOB columns
						IF EXISTS (SELECT 1 FROM sys.columns 
									WHERE OBJECT_NAME(object_id) = @table_name
									AND system_type_id IN ('34','35','99','241')
										OR (OBJECT_NAME(object_id) = @table_name
										AND system_type_id IN ('167','231','165') 
										AND max_length = -1))
							BEGIN
								SET @offline_flag = 'Y'
							END
						ELSE
							BEGIN
								SET @offline_flag = 'N'
							END
					END
				ELSE IF @clustered_ind <> '1'
					BEGIN --check if INDEX to be created has LOB columns
						IF EXISTS (SELECT 1 FROM sys.columns 
									WHERE OBJECT_NAME(object_id) = @table_name
									AND name in (select word from @tblWords)
									AND system_type_id IN ('34','35','99','241')
										OR (name in (select word from @tblWords)
										AND system_type_id IN ('167','231','165') 
										AND max_length = -1))
							BEGIN
								SET @offline_flag = 'Y'
							END
						ELSE
							BEGIN
								SET @offline_flag = 'N'
							END
					END
			END
		--NON-Enterprise Editions of SQL Server
		IF @LOBonline2012Ent = 'N' OR @LOBonline2008R2Ent = 'N'
			BEGIN
				SET @offline_flag = 'Y'
			END

		--Check if index name already exists in DB on any table.
		IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = @index_name)
			BEGIN
			--Check if index is on the correct table.
			--If it is not on the correct table, drop it.
			IF EXISTS(SELECT 1 FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock) ON si.object_id = so.object_id
					 WHERE si.name = @index_name AND so.name <> @table_name AND si.is_primary_key = 0)
			   BEGIN
				  SELECT @wrong_tb_name = (SELECT so.name FROM sys.sysindexes AS si WITH (nolock) INNER JOIN sys.sysobjects AS so WITH (nolock) ON si.id = so.id
									 WHERE si.name = @index_name AND so.name <> @table_name)
				  SET @sql = 'DROP INDEX ' + @wrong_tb_name + '.' + @index_name
			   END
			IF EXISTS(SELECT 1 FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock) ON si.object_id = so.object_id
					 WHERE si.name = @index_name AND so.name <> @table_name AND si.is_primary_key = 1)
			   BEGIN
				  SELECT @wrong_tb_name = (SELECT so.name FROM sys.sysindexes AS si WITH (nolock) INNER JOIN sys.sysobjects AS so WITH (nolock) ON si.id = so.id
									 WHERE si.name = @index_name AND so.name <> @table_name)
				  SET @sql = 'ALTER TABLE ' + @wrong_tb_name + ' DROP CONSTRAINT ' + @index_name
			   END
    
			IF (ISNULL(@pi_print_ind,'N') = 'Y' AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
			EXEC (@sql);

			--If it is on the correct table, compare index columns and included columns for all indexes EXCEPT PKs.
			--If everything doesn't match, rebuild with correct columns, using online = on for ENT clients. 
			IF EXISTS(SELECT 1 FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock) ON si.object_id = so.object_id
					  WHERE si.name = @index_name 
						AND so.name = @table_name 
						AND (dbo.fn_get_index_columns(so.object_id , si.index_id) <> @index_columns AND ISNULL(dbo.fn_get_included_columns(so.object_id , si.index_id), '') = ISNULL(@included_columns, '')
						OR dbo.fn_get_index_columns(so.object_id , si.index_id) = @index_columns AND ISNULL(dbo.fn_get_included_columns(so.object_id , si.index_id), '') <> ISNULL(@included_columns, '')
						OR dbo.fn_get_index_columns(so.object_id , si.index_id) <> @index_columns AND ISNULL(dbo.fn_get_included_columns(so.object_id , si.index_id), '') <> ISNULL(@included_columns, ''))
						AND si.is_primary_key = 0)

			   BEGIN
				  IF @ent_flag = 'Y' AND LEN(ISNULL(@included_columns, '')) > 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
						END
					 END
				  IF @ent_flag = 'Y' AND LEN(ISNULL(@included_columns, '')) = 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
						END
					 END
				  IF @ent_flag = 'N' AND LEN(ISNULL(@included_columns, '')) > 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH(DROP_EXISTING=ON)  ON NEXTGEN_INDEX_1'
						END
				 END
				  IF @ent_flag = 'N' AND LEN(ISNULL(@included_columns, '')) = 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
						END
					 END
	   
			   IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
				BEGIN TRY
				 EXEC (@sql); 
				END TRY
				BEGIN CATCH
				 --select @error_number=error_number(), @error_message=error_message()
				 PRINT 'Cannot create index: ' + '"' +@sql+ '"'
				END CATCH 
	   
			   END		  

			--If it is on the correct table, compare index columns for PKs only.
			--If everything doesn't match, rebuild with correct columns, using online = on for ENT clients. 
			IF EXISTS(SELECT 1 FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock) ON si.object_id = so.object_id
					 WHERE si.name = @index_name AND so.name = @table_name AND dbo.fn_get_index_columns(so.object_id , si.index_id) <> @index_columns AND si.is_primary_key = 1)
			   BEGIN
    			  SELECT @sql1 = 'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @index_name
				  SELECT @sql =
					 CASE 
						WHEN @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY NONCLUSTERED (' + @index_columns + ') ON NEXTGEN_INDEX_1'
						WHEN @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY NONCLUSTERED (' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
						WHEN @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY CLUSTERED (' + @index_columns + ') ON NEXTGEN_CORE'
						WHEN @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY CLUSTERED (' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_CORE'
					 END
			 
			   IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql1))) > 0) print @sql1		 
			   EXEC (@sql1);
	   
			   IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
				BEGIN TRY
				 EXEC (@sql); 
				END TRY
				BEGIN CATCH
				 --select @error_number=error_number(), @error_message=error_message()
				 PRINT 'Cannot create Primary Key: ' + '"' +@sql+ '"'
				END CATCH
				END
			END

		--UPDATE BASED ON CHANGES ONLY TO THE UNIQUE OR PRIMARY INDs
		IF EXISTS (SELECT 1 FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock)ON si.object_id = so.object_id
				  WHERE	si.name = @index_name
						AND @clustered_ind = si.type
						AND (@unique_ind <> si.is_unique OR @primary_ind <> si.is_primary_key)
						)
			BEGIN
			   IF @primary_ind = 0
			   BEGIN
				  IF @ent_flag = 'Y' AND LEN(ISNULL(@included_columns, '')) > 0
					 BEGIN
					 SELECT @sql =
						CASE --
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
						END
					 END
				  IF @ent_flag = 'Y' AND LEN(ISNULL(@included_columns, '')) = 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON, DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
						END
					 END
				  IF @ent_flag = 'N' AND LEN(ISNULL(@included_columns, '')) > 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH(DROP_EXISTING=ON)  ON NEXTGEN_INDEX_1'
						END
				 END
				  IF @ent_flag = 'N' AND LEN(ISNULL(@included_columns, '')) = 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (DROP_EXISTING=ON) ON NEXTGEN_INDEX_1'
						END
					 END
			 
			   IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql		 
				BEGIN TRY
				 EXEC (@sql); 
				END TRY
				BEGIN CATCH
				 --select @error_number=error_number(), @error_message=error_message()
				 PRINT 'Cannot create index: ' + '"' +@sql+ '"'
				END CATCH 
	   
			   END
			   ELSE
				  BEGIN
				  SELECT @sql1 = 'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @index_name
				  SELECT @sql =
					 CASE 
						WHEN @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY NONCLUSTERED (' + @index_columns + ') ON NEXTGEN_INDEX_1'
						WHEN @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY NONCLUSTERED (' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
						WHEN @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY CLUSTERED (' + @index_columns + ') ON NEXTGEN_CORE'
						WHEN @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY CLUSTERED (' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_CORE'
					 END
	    
				  IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql1))) > 0) print @sql1
				  EXEC (@sql1);
		  
				  IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
				   BEGIN TRY
					EXEC (@sql); 
				   END TRY
				   BEGIN CATCH
					--select @error_number=error_number(), @error_message=error_message()
					PRINT 'Cannot create Primary Key: ' + '"' +@sql+ '"'
				   END CATCH
				   END
			END

		--UPDATE BASED ON CHANGES ONLY TO THE CLUSTERED IND
		IF EXISTS (SELECT 1 FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock)ON si.object_id = so.object_id
				  WHERE	si.name = @index_name AND @clustered_ind <> si.type)
			BEGIN
			   SET @wrong_si_clust_ind = (SELECT si.type FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock)ON si.object_id = so.object_id
				  WHERE	si.name = @index_name AND @clustered_ind <> si.type)
			   IF @wrong_si_clust_ind IN(2, 1)
				  BEGIN
					 SET @sql = 'DROP INDEX ' + @table_name + '.' + @index_name
			 
					 IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
					 EXEC (@sql)
				  END
			   ELSE
				  BEGIN
					 SELECT @sql = 'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @index_name
			 
					 IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
					 EXEC (@sql)
				  END
			   IF @primary_ind = 0
				  BEGIN
				  IF @ent_flag = 'Y' AND LEN(ISNULL(@included_columns, '')) > 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
						END
					 END
				  IF @ent_flag = 'Y' AND LEN(ISNULL(@included_columns, '')) = 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
						END
					 END
				  IF @ent_flag = 'N' AND LEN(ISNULL(@included_columns, '')) > 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') ON NEXTGEN_INDEX_1'
						END
				 END
				  IF @ent_flag = 'N' AND LEN(ISNULL(@included_columns, '')) = 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') ON NEXTGEN_INDEX_1'
						END
					 END
			 
			   IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
				BEGIN TRY
				 EXEC (@sql); 
				END TRY
				BEGIN CATCH
				 --select @error_number=error_number(), @error_message=error_message()
				 PRINT 'Cannot create index: ' + '"' +@sql+ '"'
				END CATCH 

			   END
			   ELSE
				  BEGIN
				  SELECT @sql =
					 CASE 
						WHEN @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY NONCLUSTERED (' + @index_columns + ') ON NEXTGEN_INDEX_1'
						WHEN @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY NONCLUSTERED (' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
						WHEN @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY CLUSTERED (' + @index_columns + ') ON NEXTGEN_CORE'
						WHEN @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY CLUSTERED (' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_CORE'
					 END
			 
				  IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql		 
				   BEGIN TRY
					EXEC (@sql); 
				   END TRY
				   BEGIN CATCH
					--select @error_number=error_number(), @error_message=error_message()
					PRINT 'Cannot create Primary Key: ' + '"' +@sql+ '"'
				   END CATCH
				   END
			END

		--Check if index is named correctly.
		--If it is not named correctly, rename it using sp_rename.
		IF (SELECT COUNT(*) FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock)ON si.object_id = so.object_id
				  WHERE	so.name = @table_name
						AND dbo.fn_get_index_columns(so.object_id , si.index_id) = @index_columns
						AND ISNULL(dbo.fn_get_included_columns(so.object_id , si.index_id), '') = ISNULL(@included_columns, '')
						AND si.name <> @index_name) > 0
			BEGIN
			   SELECT 
				@wrong_inx_name = si.name
			   , @wrong_tb_name = OBJECT_NAME(si.object_id) 
			   FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock)ON si.object_id = so.object_id
						WHERE	so.name = @table_name
						AND dbo.fn_get_index_columns(so.object_id , si.index_id) = @index_columns
						AND ISNULL(dbo.fn_get_included_columns(so.object_id , si.index_id), '') = ISNULL(@included_columns, '')
						AND si.name <> @index_name

				-- Check for an existing index 
				IF (SELECT COUNT(*) FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock)ON si.object_id = so.object_id
					  WHERE	so.name = @table_name
							AND dbo.fn_get_index_columns(so.object_id , si.index_id) = @index_columns
							AND ISNULL(dbo.fn_get_included_columns(so.object_id , si.index_id), '') = ISNULL(@included_columns, '')
							AND si.name = @index_name) > 0
						BEGIN
						--Correct Index exists just drop the duplicate
							SELECT @sql = 'DROP INDEX ' + @wrong_tb_name + '.' + @wrong_inx_name 
						END
						ELSE
						BEGIN
							--No Index exists Rename the duplicate
							SELECT @sql = 'EXEC sp_rename ''' + @wrong_tb_name + '.' + @wrong_inx_name + ''', ''' + @index_name + ''', ''' + @index_type + ''' '
						END

			   IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
			   EXEC (@sql)

			   IF EXISTS(SELECT 1 FROM ng_indexes WHERE index_name = @wrong_inx_name AND table_name = @wrong_tb_name) 
				  BEGIN
					 DELETE ng_indexes  WHERE index_name =  @wrong_inx_name AND table_name = @wrong_tb_name
				  END
		   
			END

		--Check if index exists.
		--If it does not exist, create it.
		IF NOT EXISTS(SELECT 1 FROM sys.indexes AS si WITH (nolock) INNER JOIN sys.objects AS so WITH (nolock)ON si.object_id = so.object_id
				  WHERE	si.name = @index_name) 
			BEGIN
			IF @pi_primary_ind = 'N'
			   BEGIN
				  IF @ent_flag = 'Y' AND LEN(ISNULL(@included_columns, '')) > 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
						END
					 END
				  IF @ent_flag = 'Y' AND LEN(ISNULL(@included_columns, '')) = 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=OFF) ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
						END
					 END

				  IF @ent_flag = 'N' AND LEN(ISNULL(@included_columns, '')) > 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') INCLUDE(' + @included_columns + ') ON NEXTGEN_INDEX_1'
						END
					 END
				  IF @ent_flag = 'N' AND LEN(ISNULL(@included_columns, '')) = 0
					 BEGIN
					 SELECT @sql =
						CASE 
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'N' THEN 'CREATE UNIQUE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') ON NEXTGEN_INDEX_1'
							WHEN @pi_unique_ind = 'Y' AND @pi_clustered_ind = 'Y' THEN 'CREATE UNIQUE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'Y' THEN 'CREATE CLUSTERED INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') ON NEXTGEN_CORE'
							WHEN @pi_unique_ind = 'N' AND @pi_clustered_ind = 'N' THEN 'CREATE INDEX ' + @index_name + ' ON ' + @table_name + '(' + @index_columns + ') ON NEXTGEN_INDEX_1'
						END
					 END
			    
			   IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
				BEGIN TRY
				 EXEC (@sql); 
				END TRY
				BEGIN CATCH
				 --select @error_number=error_number(), @error_message=error_message()
				 PRINT 'Cannot create index: ' + '"' +@sql+ '"'
				END CATCH 

			   END
			ELSE
			   BEGIN
			   SELECT @sql =
				  CASE 
					 WHEN @pi_clustered_ind = 'N' AND @offline_flag = 'Y' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY NONCLUSTERED (' + @index_columns + ') ON NEXTGEN_INDEX_1'
					 WHEN @pi_clustered_ind = 'N' AND @offline_flag = 'N' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY NONCLUSTERED (' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_INDEX_1'
					 WHEN @pi_clustered_ind = 'Y' AND @offline_flag = 'Y' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY CLUSTERED (' + @index_columns + ') ON NEXTGEN_CORE'
					 WHEN @pi_clustered_ind = 'Y' AND @offline_flag = 'N' THEN 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY CLUSTERED (' + @index_columns + ') WITH (ONLINE=ON) ON NEXTGEN_CORE'
				  END
		  
			   IF (ISNULL(@pi_print_ind,'N') = 'Y'  AND LEN(LTRIM(RTRIM(@sql))) > 0) print @sql
				BEGIN TRY
				 EXEC (@sql); 
				END TRY
				BEGIN CATCH
				 --select @error_number=error_number(), @error_message=error_message()
				 PRINT 'Cannot create Primary Key: ' + '"' +@sql+ '"'
				END CATCH
				END
			END

		--Handling the insert into ng_indexes seperately.
		IF EXISTS (SELECT 1 FROM ng_indexes WHERE index_name = @index_name)
		BEGIN
			DELETE ng_indexes WHERE index_name = @index_name 
		END
        

		IF NOT EXISTS(SELECT 1 FROM ng_indexes WHERE index_name = @index_name) 
		BEGIN    
			INSERT INTO ng_indexes
			(table_name
			,index_name
			,index_columns
			,included_columns
			,group_name
			,primary_ind
			,clustered_ind
			,unique_ind
			,fill_factor
			,ng_system_ind
			)
			SELECT
				@table_name
				, @index_name
				, @index_columns
				, @included_columns
				, @group_name
				, @pi_primary_ind
				, @pi_clustered_ind
				, @pi_unique_ind
				, @pi_fill_factor
				, @pi_ng_system_ind
		END

		END

	SET NOCOUNT OFF;

	END    

GO


--Add ng_configure

IF OBJECT_ID(N'dbo.ng_configure') IS NOT NULL 
DROP PROCEDURE dbo.ng_configure
GO

CREATE PROCEDURE ng_configure
		@pi_reconfigure_regardless BIT = 0
AS
--{5.8.3.47}  
----------------------  
-- $Change: 378374 $  
-- $DateTime: 2015/11/17 13:36:57 $  
-- $Author: scellini $  
----------------------  
  
-- This proc changes the server configuration so index build and upgrade execute faster  
SET NOCOUNT ON;
BEGIN 

	DECLARE	  @dbn SYSNAME
			, @db_is_Prod BIT
			, @multiple_Prod_dbs BIT
			, @RecoveryModel VARCHAR(30)
			, @IsMirror VARCHAR(20)


	SELECT @dbn = DB_NAME()


	IF @dbn NOT LIKE '%Prod%' OR @dbn LIKE '%Audit%'
		BEGIN
			SELECT @db_is_Prod = 0
		END


	IF @dbn LIKE '%Prod%' AND @dbn NOT LIKE '%Audit%'
		BEGIN
			SELECT @db_is_Prod = 1
		END


	IF (SELECT COUNT(*) FROM master..sysdatabases WHERE name like '%Prod%' AND name NOT LIKE '%Audit%') > 1 
		BEGIN
			SELECT @multiple_Prod_dbs = 1
		END
	ELSE 
		BEGIN
			SELECT @multiple_Prod_dbs = 0
		END

	IF @pi_reconfigure_regardless = 1 
	BEGIN
		SELECT	@multiple_Prod_dbs = 0
				, @db_is_Prod = 1
	END

	IF @multiple_Prod_dbs = 0 AND @db_is_Prod = 1
BEGIN
	BEGIN

		IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE   TABLE_CATALOG=DB_NAME()  
													 and TABLE_SCHEMA ='dbo' and TABLE_NAME='ng_sp_configure'  and  TABLE_TYPE ='BASE TABLE')  
		BEGIN  
			PRINT 'The table dbo.ng_sp_configure exists; please run ng_reconfigure first.'  
			RETURN  
		END 
 
	END

		CREATE TABLE dbo.ng_sp_configure  
		(config_name  SYSNAME,  
		 config_min   INT,  
		 config_max   INT,  
		 config_val INT,  
		 run_val    INT)  
  

	SELECT @dbn=DB_NAME()  
  
	EXEC sp_configure 'show advanced options', 1;  
	RECONFIGURE WITH OVERRIDE

	IF EXISTS (SELECT 1  FROM sys.database_mirroring WHERE database_id = DB_ID() and mirroring_state IS NULL)
		BEGIN
			SELECT @IsMirror = 'N'
		END
		ELSE
		BEGIN
			SELECT @isMirror = 'Y'
		END
    
	SELECT @RecoveryModel=CASE recovery_model_desc  WHEN 'FULL' THEN 1
													WHEN 'SIMPLE' THEN 2
													WHEN 'BULK_LOGGED' THEN 3 END
	FROM sys.databases WHERE database_id =  DB_ID()

	IF @isMirror = 'N'
	  BEGIN  
		--IF FLOOR(LEFT('0'+convert(varchar(20),SERVERPROPERTY('productversion')),5)) < 9  
		--  BEGIN  
		--      exec sp_dboption @dbn,'select into/bulkcopy', true  
		--  END  
		--ELSE  
		--  BEGIN   
			   EXEC ('ALTER DATABASE ['+ @dbn + '] SET RECOVERY BULK_LOGGED')  
			   --exec sp_dboption @dbn,'select into/bulkcopy', true  
		--  END
	 END
  
	INSERT INTO dbo.ng_sp_configure (config_name,config_min,config_max,config_val,run_val)  
	EXEC sp_configure  
  
	EXEC sp_configure 'affinity mask',0  
  
	EXEC sp_configure 'cost threshold for parallelism',10   
  
	EXEC sp_configure 'max degree of parallelism',0  
  
	RECONFIGURE WITH OVERRIDE

	 INSERT INTO dbo.ng_sp_configure (config_name,config_min,config_max,config_val,run_val)  
	  VALUES('RECOVERY',0,0,0,@RecoveryModel)
END 
ELSE 
	PRINT 'Skipping reconfigurations due to the database being either non-Prod, Audit related, or there are multiple Prod DBs detected on this SQL Server instance'

SET NOCOUNT OFF;
END
GO


--Add ng_reconfigure

IF  EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE   ROUTINE_CATALOG=DB_NAME() and ROUTINE_SCHEMA ='dbo' and ROUTINE_NAME='ng_reconfigure'  and  ROUTINE_TYPE ='procedure')
DROP PROCEDURE dbo.ng_reconfigure
GO
CREATE PROCEDURE dbo.ng_reconfigure
		@pi_reconfigure_regardless BIT = 0
AS
--{5.8.3.47}  
----------------------  
-- $Change: 378374 $  
-- $DateTime: 2015/11/17 13:36:57 $  
-- $Author: scellini $  
----------------------  
  
-- This proc changes the server configuration back to the client settings after index build and upgrades  
  
	DECLARE	  @affmas INT  
			, @ctfp INT  
			, @maxdop INT  
			, @dbn SYSNAME  
			, @RecoveryModel VARCHAR(30)
			, @IsMirror VARCHAR(20)
			, @db_is_Prod BIT
			, @multiple_Prod_dbs BIT

SET NOCOUNT ON; 
SELECT @dbn=DB_NAME() 


	IF @dbn NOT LIKE '%Prod%' OR @dbn LIKE '%Audit%'
		BEGIN
			SELECT @db_is_Prod = 0
		END


	IF @dbn LIKE '%Prod%' AND @dbn NOT LIKE '%Audit%'
		BEGIN
			SELECT @db_is_Prod = 1
		END


	IF (SELECT COUNT(*) FROM master..sysdatabases WHERE name like '%Prod%' AND name NOT LIKE '%Audit%') > 1 
		BEGIN
			SELECT @multiple_Prod_dbs = 1
		END
	ELSE 
		BEGIN
			SELECT @multiple_Prod_dbs = 0
		END

	IF @pi_reconfigure_regardless = 1 
	BEGIN
		SELECT	@multiple_Prod_dbs = 0
				, @db_is_Prod = 1
	END

	IF @multiple_Prod_dbs = 0 AND @db_is_Prod = 1

BEGIN
	BEGIN
		IF EXISTS (SELECT 1  FROM sys.database_mirroring WHERE database_id = DB_ID() AND mirroring_state IS NULL)
			BEGIN
				SELECT @IsMirror = 'N'
			END
		ELSE
			BEGIN
				SELECT @isMirror = 'Y'
			END
   
  
IF @isMirror = 'N'
  BEGIN
    --IF FLOOR(LEFT('0'+convert(varchar(20),SERVERPROPERTY('productversion')),5)) < 9  
    --BEGIN  
    --    exec sp_dboption @dbn,'select into/bulkcopy', FALSE  
    --END  
    --ELSE  
    --BEGIN 
   
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE   TABLE_CATALOG=DB_NAME()  
                                             and TABLE_SCHEMA ='dbo' and TABLE_NAME='ng_sp_configure'  and  TABLE_TYPE ='BASE TABLE')  
        BEGIN
            PRINT 'Error: Either  dbo.ng_sp_configure was  not created in the dbo schema, or ng_configure was not executed or it failed' 
            RETURN  
        END  
        
        SELECT @RecoveryModel=CASE run_val WHEN 1 then 'FULL'
	                                     WHEN 2 then 'SIMPLE'
	                                     WHEN 3 THEN 'BULK_LOGGED' END
        FROM dbo.ng_sp_configure WHERE config_name='RECOVERY' 

        EXEC ('ALTER DATABASE ['+ @dbn + '] SET RECOVERY '+@RecoveryModel)  
        --exec sp_dboption @dbn,'select into/bulkcopy', FALSE  
    --END
  END
  
  

  
SELECT  @affmas=run_val FROM dbo.ng_sp_configure WHERE config_name='affinity mask'  
SELECT  @ctfp=run_val FROM dbo.ng_sp_configure WHERE config_name='cost threshold for parallelism'  
SELECT  @maxdop=run_val FROM dbo.ng_sp_configure WHERE config_name='max degree of parallelism'  
  
  
EXEC sp_configure 'affinity mask',@affmas   
  
EXEC sp_configure 'cost threshold for parallelism',@ctfp   
  
EXEC sp_configure 'max degree of parallelism',@maxdop  
  
RECONFIGURE WITH OVERRIDE 
  
DROP TABLE dbo.ng_sp_configure  
  
  END
END 
ELSE 
	PRINT 'Skipping reconfigurations due to the database being either non-Prod, Audit related, or there are multiple Prod DBs detected on this SQL Server instance'

SET NOCOUNT OFF;
GO


-- Add NG Weekly Maintenance

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ng_add_weekly_maintenance]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE ng_add_weekly_maintenance
GO

CREATE PROCEDURE ng_add_weekly_maintenance 
 (@pi_base_path as nvarchar(255))
AS
--{5.6.6.4}
---------------------- 
-- $Change: 175993 $
-- $DateTime: 2012/05/10 11:13:24 $
-- $Author: darrenr $
----------------------
/*****************************************************************************************************
This procedure adds the weekly maintenance jobs.

2.75a.054  03/07/2002  MBT  DEV2 8435  New Procedure
5.  0.  4  03/04/2005  RMC             Renamed and added to 5.0
5.  3.  31 09/12/2005  RMC             MP switch added
5.4.14     04/10/2006  JPA             Parameter naming conventions
5.4.26     06/28/2006  rmc             default logging / job step for ng_check_indexes
5.5.22     02/28/2007  rmc             default logging / job step for ng_check_indexes
5.5.27.1   02/28/2007  rmc             added parallelism for performance
5.6.2.61   08/27/2009  RMC             fix issue when "local" server name is not defined
5.6.6.4   01/27/2011  RMC             log file name correction
******************************************************************************************************/

DECLARE @JobID BINARY(16)  
DECLARE @TempName as nvarchar(255) 
DECLARE @TempCommand as nvarchar(255) 
DECLARE @TempDesc as nvarchar(255) 
DECLARE @TempOutput as nvarchar(255) 
DECLARE @dbname as sysname 
DECLARE @stepname as nvarchar(255)
DECLARE @schedname as nvarchar(255)
DECLARE @SRVR_NM as sysname 

select @SRVR_NM = rtrim(@@servername)
set @dbname = rtrim(db_name())


-- Add NextGen EPM category
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = 'NextGen') = 0 
    EXECUTE msdb.dbo.sp_add_category @name = 'NextGen'

-- Delete job
SELECT @TempName = '' + @dbname + ' - Weekly Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = @TempName) = 1
BEGIN           
    EXECUTE msdb.dbo.sp_delete_job @job_name = @TempName
    
END

SELECT @TempDesc = 'Weekly Maintenance for ' + @dbname
--RC
EXECUTE  msdb.dbo.sp_add_job @job_name=@TempName, 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=@TempDesc, 
		@category_name=N'NextGen', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
		
-- Add the job steps
if left(@pi_base_path,2) = '<<' or @pi_base_path = '' or @pi_base_path is null
    SELECT @TempOutput = '' 
else
    SELECT @TempOutput = '' + @pi_base_path + '\DBBackup\WeeklyMaintenance_' + @dbname + '.log'
		
--RC
/****** Object:  Step [prestep: ng_configure to use parallelism and performance]    Script Date: 03/06/2008 12:57:12 ******/
EXECUTE msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'prestep: ng_configure to use parallelism and performance', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec ng_configure', 
		@database_name=@dbname, 
		@output_file_name=@TempOutput, 
		@flags=2
--RC
/****** Object:  Step [ngtest56 - DBCC CHECKDB]    Script Date: 03/06/2008 12:57:12 ******/
set @stepname = @dbname +' - DBCC CHECKDB'
set @tempname= 'DBCC CHECKDB ('+@dbname+')'
EXECUTE msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@stepname, 
            @step_id=2, 
            @cmdexec_success_code=0, 
            @on_success_action=3, 
            @on_success_step_id=0, 
            @on_fail_action=3, 
            @on_fail_step_id=0, 
            @retry_attempts=2, 
            @retry_interval=1, 
            @os_run_priority=0, @subsystem=N'TSQL', 
            @command=@tempname, 
            @database_name=N'master', 
            @output_file_name=@TempOutput, 
	        @flags=2
--RC
/****** Object:  Step [ngtest56 - Rebuild Indexes]    Script Date: 03/06/2008 12:57:13 ******/
set @stepname = @dbname +' - Rebuild Indexes'
EXECUTE msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@stepname, 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=2, 
		@retry_interval=1, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'ng_check_indexes ''R'', ''DI,RO,RC,RN,MP,MC,MN,US25''', 
		@database_name=@dbname, 
		@output_file_name=@TempOutput, 
		@flags=4
--RC
/****** Object:  Step [poststep: reset server back to transactional setting]    Script Date: 03/06/2008 12:57:13 ******/
EXECUTE msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'poststep: reset server back to transactional setting', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec ng_reconfigure', 
		@database_name=@dbname, 
		@output_file_name=@TempOutput, 
		@flags=2
--RC
EXECUTE msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
--RC

-- Add the job schedules
set @schedname = @dbname + ' - Weekly Maintenance Schedule'

EXECUTE msdb.dbo.sp_add_jobschedule @job_id = @JobID, 
    @name = @TempName, @enabled = 1, @freq_type = 8, 
    @active_start_date = 19990118, @active_start_time = 200000, 
    @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, 
    @active_end_date = 99991231, @active_end_time = 235959

-- Add the Target Servers
EXECUTE msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = @SRVR_NM
GO


--IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = rtrim(ltrim(db_name())) + ' - Weekly Maintenance')
--execute ng_add_weekly_maintenance ''
--go



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ng_add_UD3_one_time]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE ng_add_UD3_one_time
GO

CREATE PROCEDURE ng_add_UD3_one_time 
AS
----------------------
/*****************************************************************************************************
This procedure adds the verifies the Advanced Audit Indexes
*/
DECLARE @JobID BINARY(16)  
DECLARE @TempName as nvarchar(255) 
DECLARE @TempCommand as nvarchar(255) 
DECLARE @TempDesc as nvarchar(255) 
DECLARE @TempOutput as nvarchar(255) 
DECLARE @dbname as sysname 
DECLARE @stepname as nvarchar(255)
DECLARE @schedname as nvarchar(255)
DECLARE @SRVR_NM as sysname 

select @SRVR_NM = rtrim(@@servername)
set @dbname = rtrim(db_name())
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = 'NextGen') = 0 
    EXECUTE msdb.dbo.sp_add_category @name = 'NextGen'

-- Delete job
SELECT @TempName = '' + @dbname + ' - UD3 One Time Indexing'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = @TempName) = 1
BEGIN           
    EXECUTE msdb.dbo.sp_delete_job @job_name = @TempName
    
END

SELECT @TempDesc = 'UD3 One Time Indexing for ' + @dbname
--RC
EXECUTE  msdb.dbo.sp_add_job @job_name=@TempName, 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=@TempDesc, 
		@category_name=N'NextGen', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXECUTE msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'prestep: ng_configure to use parallelism and performance', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec ng_configure', 
		@database_name=@dbname, 
		@flags=2
--RC
/****** Object:  Step [ngtest56 - DBCC CHECKDB]    Script Date: 03/06/2008 12:57:12 ******/
set @stepname = @dbname +' - Indexing'
EXECUTE msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@stepname, 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=2, 
		@retry_interval=1, 
		@os_run_priority=0, @subsystem=N'TSQL', 
			@command=N'--Check for index on event_data_trail beginning with event_id.  If missing, create the index on event_id
IF EXISTS
(SELECT 1 FROM sys.index_columns ic
JOIN sys.objects o ON o.object_id = ic.object_id
JOIN sys.indexes i ON i.index_id = ic.index_id AND i.object_id = ic.object_id
JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE o.name = ''event_data_trail'' AND c.name = ''event_id'' and ic.index_column_id = 1)
BEGIN
IF EXISTS (SELECT si.NAME FROM sys.indexes si JOIN sys.tables st on si.object_id = st.object_id
WHERE si.name = ''inx_event_data_trail_1'' AND st.name = ''event_data_trail'')  
      BEGIN
            DROP INDEX event_data_trail.inx_event_data_trail_1;
      END

CREATE NONCLUSTERED INDEX inx_event_data_trail_1 ON event_data_trail 
(event_id)
ON NEXTGEN_INDEX_1
END
--GO

--Drop the clustered PK on event_id from event_audit_mstr

IF EXISTS 
(SELECT 1 FROM sys.indexes i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.index_columns ic ON ic.object_id = o.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_id
WHERE o.name = ''event_audit_mstr'' 
AND i.is_primary_key = 1 
AND i.type = 1
AND c.name = ''event_id'')
BEGIN
IF  EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = ''PRIMARY KEY''
    AND TABLE_NAME = ''event_audit_mstr'' AND CONSTRAINT_NAME = ''PK_event_audit_mstr''
    AND TABLE_SCHEMA =''dbo'' )
alter table dbo.event_audit_mstr drop constraint [PK_event_audit_mstr]
END
--GO


--Create clustered index on event_timestamp column in event_audit_mstr

IF NOT EXISTS
(SELECT 1 FROM sys.indexes i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.index_columns ic ON ic.object_id = o.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_id
WHERE o.name = ''event_audit_mstr'' 
AND i.type = 1
OR i.name IN (''IX_event_audit_master'', ''inx_event_audit_mstr1'')) 
BEGIN
CREATE CLUSTERED INDEX [inx_event_audit_mstr1] ON [dbo].[event_audit_mstr]
(
	[event_timestamp] ASC
) ON nextgen_core
END
--GO

--Recreate a nonclustered pk on event_audit_mstr

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = ''PRIMARY KEY''
    AND TABLE_NAME = ''event_audit_mstr'' 
    AND TABLE_SCHEMA =''dbo'' )
alter table dbo.event_audit_mstr add constraint [PK_event_audit_mstr] primary key nonclustered (event_id) on nextgen_index_1
--GO

IF NOT EXISTS
(SELECT 1 FROM sys.index_columns ic
JOIN sys.objects o ON o.object_id = ic.object_id
JOIN sys.indexes i ON i.index_id = ic.index_id AND i.object_id = ic.object_id
JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE o.name = ''event_security'' AND c.name = ''event_id'' and ic.index_column_id = 1)
BEGIN
CREATE NONCLUSTERED INDEX [inx_event_security1] ON [dbo].event_security
(
	[event_id] ASC
) ON [NEXTGEN_INDEX_1]
END
--GO


--Add nonclustered index to event_audit_mstr for Audit Reporting Search

IF EXISTS (SELECT si.name FROM sys.indexes si JOIN sys.tables st ON si.object_id = st.object_id JOIN sys.index_columns sic ON sic.object_id = si.object_id AND sic.index_id = si.index_id JOIN sys.columns sc ON sc.object_id = sic.object_id AND sc.column_id = sic.column_id
WHERE si.name = (''inx_event_audit_mstr1'') AND st.name = ''event_audit_mstr'' AND sc.name = ''event_timestamp'' AND si.type = 2)  
      BEGIN
            DROP INDEX event_audit_mstr.inx_event_audit_mstr1;
      END
IF EXISTS (SELECT si.name FROM sys.indexes si JOIN sys.tables st ON si.object_id = st.object_id JOIN sys.index_columns sic ON sic.object_id = si.object_id AND sic.index_id = si.index_id JOIN sys.columns sc ON sc.object_id = sic.object_id AND sc.column_id = sic.column_id
WHERE si.name = (''inx_event_audit_mstr_1'') AND st.name = ''event_audit_mstr'' AND sc.name = ''event_timestamp'')  
      BEGIN
            DROP INDEX event_audit_mstr.inx_event_audit_mstr_1;
      END
IF NOT EXISTS (SELECT si.name FROM sys.indexes si JOIN sys.tables st ON si.object_id = st.object_id JOIN sys.index_columns sic ON sic.object_id = si.object_id AND sic.index_id = si.index_id JOIN sys.columns sc ON sc.object_id = sic.object_id AND sc.column_id = sic.column_id
WHERE si.name = (''inx_event_audit_mstr2'') AND st.name = ''event_audit_mstr'')
BEGIN
		CREATE NONCLUSTERED INDEX inx_event_audit_mstr2 ON event_audit_mstr (enterprise_id, practice_id, action_id, person_id, created_by) INCLUDE (event_id, category_id, action_type, source_product_description, station_id, event_message, hash_code, process_id, site_id, location_Id, event_timestamp_timezone) ON NEXTGEN_INDEX_1
END
--GO', 
		@database_name=@dbname, 
		@flags=2
--RC
/****** Object:  Step [poststep: reset server back to transactional setting]    Script Date: 03/06/2008 12:57:13 ******/
EXECUTE msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'poststep: reset server back to transactional setting', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec ng_reconfigure', 
		@database_name=@dbname, 
		@flags=2
--RC
EXECUTE msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
--RC

-- Add the job schedules
set @schedname = @dbname + ' - Weekly Maintenance Schedule'

declare @start_date varchar(8)
set @start_date =  convert(varchar(8),getdate(),112)
EXECUTE msdb.dbo.sp_add_jobschedule @job_id = @JobID, 
    @name = @TempName, @enabled=1, 
		@freq_type=1, 
		@freq_interval=0, 
		@freq_subday_type=0, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=@start_date, 
		@active_end_date=99991231, 
		@active_start_time=200000, 
		@active_end_time=235959

-- Add the Target Servers
EXECUTE msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = @SRVR_NM
GO


IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = rtrim(ltrim(db_name())) + ' - UD3 One Time Indexing')
execute ng_add_UD3_one_time
GO

SET NOEXEC OFF
