Use ATNDBTest
Go

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

If Exists(Select * From sys.objects Where name = 'FactorHeaderAndDetailAbstractList') 
Begin
	DROP VIEW dbo.FactorHeaderAndDetailAbstractList
End

If Exists(Select * From sys.objects Where name = 'DF_Broker_Active') 
Begin
	ALTER TABLE dbo.Broker
		DROP CONSTRAINT DF_Broker_Active
End


GO
CREATE TABLE dbo.Tmp_Broker
	(
	Serial bigint NOT NULL,
	Code bigint NOT NULL,
	Name varchar(50) NULL,
	BrokerKindRef smallint NULL,
	Active bit NULL,
	Manager varchar(50) NULL,
	Address varchar(150) NULL,
	PostCode varchar(20) NULL,
	Tel1 varchar(20) NULL,
	Tel2 varchar(20) NULL,
	Fax varchar(20) NULL,
	Mobile varchar(20) NULL,
	SSN varchar(50) NULL,
	AccEntityL1CodeBookRef bigint NULL,
	AccEntityL2CodeBookRef bigint NULL,
	SaleBrokerPercent decimal(6, 3) NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	Description varchar(300) NULL,
	OfficeDetailRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Broker SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_Broker ADD CONSTRAINT
	DF_Broker_Active DEFAULT ((0)) FOR Active
GO
IF EXISTS(SELECT * FROM dbo.Broker)
	 EXEC('INSERT INTO dbo.Tmp_Broker (Serial, Code, Name, Active, Manager, Address, PostCode, Tel1, Tel2, Fax, Mobile, SSN, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, SaleBrokerPercent, RecorderRef, RecordDate, Description, OfficeDetailRef)
		SELECT Serial, Code, Name, Active, Manager, Address, PostCode, Tel1, Tel2, Fax, Mobile, SSN, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, SaleBrokerPercent, RecorderRef, RecordDate, Description, OfficeDetailRef FROM dbo.Broker WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Broker
GO
EXECUTE sp_rename N'dbo.Tmp_Broker', N'Broker', 'OBJECT' 
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	PK_Broker PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_AccEntityL1CodeBook FOREIGN KEY
	(
	AccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_AccEntityL2CodeBook FOREIGN KEY
	(
	AccEntityL2CodeBookRef
	) REFERENCES dbo.AccEntityL2CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_Users FOREIGN KEY
	(
	RecorderRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO


BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactorHeader ADD CONSTRAINT
	FK_FactorHeader_Broker FOREIGN KEY
	(
	BrokerRef
	) REFERENCES dbo.Broker
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.FactorHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT




update Broker set BrokerKindRef = 2