--CVH 2017-03-02 Increase size of controlText(varchar50) field to controlText(varchar150)

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT FK_pal_SurfaceField_pal_Surface
GO
ALTER TABLE dbo.pal_Surface SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isActive
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isGrid
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isFilter
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_defaultToCurrent
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isControlled
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isAutoPostback
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isHidden
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isReadOnly
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isUnique
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isComparable
GO
ALTER TABLE dbo.pal_SurfaceField
	DROP CONSTRAINT DF_pal_SurfaceField_isHiddenFromView
GO
CREATE TABLE dbo.Tmp_pal_SurfaceField
	(
	recId int NOT NULL IDENTITY (1, 1),
	surfaceId int NOT NULL,
	surfaceFieldName varchar(150) NOT NULL,
	surfaceFieldDisplay varchar(150) NOT NULL,
	surfaceFieldTypeId int NOT NULL,
	length int NULL,
	required bit NULL,
	lookupCategory int NULL,
	relationalFields varchar(250) NULL,
	relationalValues varchar(250) NULL,
	relationalOrderBy varchar(250) NULL,
	relationalSurface varchar(250) NULL,
	relationalObject varchar(250) NULL,
	parentId int NULL,
	sequence int NULL,
	isActive bit NULL,
	isGrid bit NULL,
	isFilter bit NULL,
	colSequence int NULL,
	contentId int NULL,
	defaultToCurrent bit NULL,
	isControlled bit NULL,
	controlledValue varchar(50) NULL,
	controlText varchar(150) NULL,
	isAutoPostback bit NULL,
	isHidden bit NULL,
	isReadOnly bit NULL,
	placeholder varchar(250) NULL,
	isUnique bit NULL,
	actionType int NULL,
	action int NULL,
	actionSource int NULL,
	isComparable bit NULL,
	alternateView bit NULL,
	isHiddenFromWizzard bit NULL,
	surfaceDateTypeId int NULL,
	isHiddenFromView bit NULL,
	actionValue varchar(250) NULL,
	surfaceFieldSize int NULL,
	isLabelHidden bit NULL,
	isOnNewLine bit NULL,
	labelSize int NULL,
	isHiddenFromForm bit NULL,
	accessLevel int NULL,
	hasBottomBorder bit NULL,
	hasFullBorder bit NULL,
	defaultValue varchar(250) NULL,
	validationType int NULL,
	enableBulkUpdate bit NULL,
	isCurrency bit NULL,
	isVerticalHeader bit NULL,
	gridSequence int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isActive DEFAULT ((0)) FOR isActive
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isGrid DEFAULT ((0)) FOR isGrid
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isFilter DEFAULT ((0)) FOR isFilter
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_defaultToCurrent DEFAULT ((0)) FOR defaultToCurrent
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isControlled DEFAULT ((0)) FOR isControlled
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isAutoPostback DEFAULT ((0)) FOR isAutoPostback
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isHidden DEFAULT ((0)) FOR isHidden
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isReadOnly DEFAULT ((0)) FOR isReadOnly
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isUnique DEFAULT ((0)) FOR isUnique
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isComparable DEFAULT ((0)) FOR isComparable
GO
ALTER TABLE dbo.Tmp_pal_SurfaceField ADD CONSTRAINT
	DF_pal_SurfaceField_isHiddenFromView DEFAULT ((0)) FOR isHiddenFromView
GO
SET IDENTITY_INSERT dbo.Tmp_pal_SurfaceField ON
GO
IF EXISTS(SELECT * FROM dbo.pal_SurfaceField)
	 EXEC('INSERT INTO dbo.Tmp_pal_SurfaceField (recId, surfaceId, surfaceFieldName, surfaceFieldDisplay, surfaceFieldTypeId, length, required, lookupCategory, relationalFields, relationalValues, relationalOrderBy, relationalSurface, relationalObject, parentId, sequence, isActive, isGrid, isFilter, colSequence, contentId, defaultToCurrent, isControlled, controlledValue, controlText, isAutoPostback, isHidden, isReadOnly, placeholder, isUnique, actionType, action, actionSource, isComparable, alternateView, isHiddenFromWizzard, surfaceDateTypeId, isHiddenFromView, actionValue, surfaceFieldSize, isLabelHidden, isOnNewLine, labelSize, isHiddenFromForm, accessLevel, hasBottomBorder, hasFullBorder, defaultValue, validationType, enableBulkUpdate, isCurrency, isVerticalHeader, gridSequence)
		SELECT recId, surfaceId, surfaceFieldName, surfaceFieldDisplay, surfaceFieldTypeId, length, required, lookupCategory, relationalFields, relationalValues, relationalOrderBy, relationalSurface, relationalObject, parentId, sequence, isActive, isGrid, isFilter, colSequence, contentId, defaultToCurrent, isControlled, controlledValue, controlText, isAutoPostback, isHidden, isReadOnly, placeholder, isUnique, actionType, action, actionSource, isComparable, alternateView, isHiddenFromWizzard, surfaceDateTypeId, isHiddenFromView, actionValue, surfaceFieldSize, isLabelHidden, isOnNewLine, labelSize, isHiddenFromForm, accessLevel, hasBottomBorder, hasFullBorder, defaultValue, validationType, enableBulkUpdate, isCurrency, isVerticalHeader, gridSequence FROM dbo.pal_SurfaceField WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_pal_SurfaceField OFF
GO
DROP TABLE dbo.pal_SurfaceField
GO
EXECUTE sp_rename N'dbo.Tmp_pal_SurfaceField', N'pal_SurfaceField', 'OBJECT' 
GO
ALTER TABLE dbo.pal_SurfaceField ADD CONSTRAINT
	PK_pal_SurfaceDataFields PRIMARY KEY CLUSTERED 
	(
	recId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_SurfaceField_surfaceId ON dbo.pal_SurfaceField
	(
	surfaceId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_SurfaceField_surfaceFieldName ON dbo.pal_SurfaceField
	(
	surfaceFieldName
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.pal_SurfaceField ADD CONSTRAINT
	FK_pal_SurfaceField_pal_Surface FOREIGN KEY
	(
	surfaceId
	) REFERENCES dbo.pal_Surface
	(
	recId
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT