<cfif form.Type EQ "Patch">
	<cfinclude template="patch_13.5.08.cfm">
</cfif>

<cfquery datasource="#application.config.DSN#">

set noexec off
Begin

declare 
	@LocationA_Collation varchar(200),
	@LocationB_Collation varchar(200),
	@qtdCharacter int = 0,
	@fieldSize int = 900,
	@tableName varchar(256),
	@fieldName varchar(256),
	@parent_object_id int,
	@DefaultConstraintName varchar(256)


--check the Object_ID of table FormResponseField
set @tableName = 'FormResponseField'
set @parent_object_id = (select object_id(@tableName))

--check the current Collation type for the fields LocationA and LocationB on the table FormResponseField
	SELECT 
		@LocationA_Collation = collation_name
	FROM sys.columns
	WHERE 
		OBJECT_ID IN (
				SELECT 
					OBJECT_ID
				FROM sys.objects
				WHERE 
					type = 'U'
					AND 
					name = 'FormResponseField')
		AND 
		name = 'LocationA'

	SELECT 
		@LocationB_Collation = collation_name
	FROM sys.columns
	WHERE 
		OBJECT_ID IN (
				SELECT 
					OBJECT_ID
				FROM sys.objects
				WHERE 
					type = 'U'
					AND 
					name = 'FormResponseField')
		AND 
		name = 'LocationB'
	
	
	--check if we have a field locationA bigger than @fieldSize
	SELECT @qtdCharacter = COUNT(*) FROM FormResponseField WHERE len(LocationA) >= @fieldSize
	
	if NOT @qtdCharacter = 0
		begin
			print 'You can not execute that script. Please contact Intranet Connections as soon as possible.'
			set noexec on
		end
	end
	
	--check if we have a field locationB bigger than @fieldSize
	SELECT @qtdCharacter = COUNT(*) FROM FormResponseField WHERE len(LocationB) >= @fieldSize
	
	if NOT @qtdCharacter = 0
		begin
			print 'You can not execute that script. Please contact Intranet Connections as soon as possible.'
			set noexec on
		end
	else
		begin

			--delete CONSTRAINT
			
			Set @fieldName = 'RowNo'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
	
				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')

			Set @fieldName = 'PossibleScore'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')
			
			Set @fieldName = 'Score'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)

				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')
			
			Set @fieldName = 'SelectedOptions'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')
			
			Set @fieldName = 'LocationA'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')
			
			Set @fieldName = 'LocationAbbrevA'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')
			
			Set @fieldName = 'LocationB'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')
			
			Set @fieldName = 'LocationAbbrevB'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')
			
			Set @fieldName = 'RoundTrip'
			IF EXISTS (select d.name [column] 
						from sys.default_constraints d
						join sys.tables t on t.object_id = d.parent_object_id
						join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
						where 
							d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				set @defaultConstraintName = (select d.name [column] 
												from sys.default_constraints d
												join sys.tables t on t.object_id = d.parent_object_id
												join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
												where 
													d.parent_object_id = @parent_object_id and c.name = @fieldName)
				
				EXEC ('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @defaultConstraintName + ']')
			
			IF EXISTS (SELECT * FROM sys.objects WHERE type = 'D' AND name = 'DF_FormResponseField_LocationB')
				ALTER TABLE [FormResponseField] DROP CONSTRAINT [DF_FormResponseField_LocationB]


			IF EXISTS (SELECT * FROM sys.objects WHERE type = 'D' AND name = 'DF_FormResponseField_MileageData')
				ALTER TABLE [FormResponseField] DROP CONSTRAINT [DF_FormResponseField_MileageData]


			--delete INDEX
			
			IF EXISTS(	select 
							t.name as tablename, 
							i.* 
						from sys.indexes i, sys.tables t
						where 
							i.object_id = t.object_id
							and 
							i.type_desc = 'NONCLUSTERED'
							and 
							i.name = 'IX_FormResponseField_LocationA')
	
				DROP INDEX [IX_FormResponseField_LocationA] ON [dbo].[FormResponseField]

			IF EXISTS(	select 
							t.name as tablename, 
							i.* 
						from sys.indexes i, sys.tables t
						where 
							i.object_id = t.object_id
							and 
							i.type_desc = 'NONCLUSTERED'
							and 
							i.name = 'IX_FormResponseField_LocationB')
	
				DROP INDEX [IX_FormResponseField_LocationB] ON [dbo].[FormResponseField]


			--alter TABLE with the current Collation
			
			EXEC ('ALTER TABLE [' + @tableName + '] ALTER COLUMN LocationA varchar(900) COLLATE ' + @LocationA_Collation + ' not null')
			EXEC ('ALTER TABLE [' + @tableName + '] ALTER COLUMN LocationB varchar(900) COLLATE ' + @LocationB_Collation + ' not null')
			

			--create CONSTRAINT
			
			Set @fieldName = 'RowNo'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			Set @fieldName = 'PossibleScore'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			Set @fieldName = 'Score'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			Set @fieldName = 'SelectedOptions'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			Set @fieldName = 'LocationA'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			Set @fieldName = 'LocationAbbrevA'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			Set @fieldName = 'LocationB'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			Set @fieldName = 'LocationAbbrevB'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			Set @fieldName = 'RoundTrip'
			IF NOT EXISTS (select d.name [column] 
							from sys.default_constraints d
							join sys.tables t on t.object_id = d.parent_object_id
							join sys.columns c on c.object_id = t.object_id and	c.column_id = d.parent_column_id
							where 
								d.parent_object_id = @parent_object_id and c.name = @fieldName)
	
				EXEC ('ALTER TABLE [' + @tableName + '] ADD CONSTRAINT [DF_' + @tableName + '_' + @fieldName + '] default('''') FOR [' + @fieldName + ']')

			
			--create INDEX
			
			--LocationA

			IF NOT EXISTS(	select 
								t.name as tablename, 
								i.* 
							from sys.indexes i, sys.tables t
							where 
								i.object_id = t.object_id
								and 
								i.type_desc = 'NONCLUSTERED'
								and 
								i.name = 'IX_FormResponseField_LocationA')

				CREATE NONCLUSTERED INDEX [IX_FormResponseField_LocationA] ON [dbo].[FormResponseField]
				(
					[LocationA] ASC
				)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)


			--LocationB
			IF NOT EXISTS(	select 
								t.name as tablename, 
								i.* 
							from sys.indexes i, sys.tables t
							where 
								i.object_id = t.object_id
								and 
								i.type_desc = 'NONCLUSTERED'
								and 
								i.name = 'IX_FormResponseField_LocationB')
				CREATE NONCLUSTERED INDEX [IX_FormResponseField_LocationB] ON [dbo].[FormResponseField]
				(
					[LocationB] ASC
				)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

END


</cfquery>