Use ATNDBTest
Go

/*
   Thursday, March 30, 20174:21:47 AM
   User: 
   Server: TAHERIAN
   Database: ATNDBTest
   Application: 
*/

/* 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.Part
	DROP CONSTRAINT FK_Parts_Unit
GO
ALTER TABLE dbo.Units SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT FK_Parts_Storages
GO
ALTER TABLE dbo.Storages SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT FK_Parts_PartPriceKind
GO
ALTER TABLE dbo.PartBuyPriceKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT FK_Parts_PartKind
GO
ALTER TABLE dbo.PartKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT FK_Parts_Groups
GO
ALTER TABLE dbo.Groups SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT FK_Part_PartSalePriceScope
GO
ALTER TABLE dbo.PartSalePriceScope SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Part
	DROP CONSTRAINT FK_Part_PartProfitPercentKind
GO
ALTER TABLE dbo.PartProfitPercentKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
If Exists(Select * From sys.Views Where name = 'FactorHeaderAndDetailAbstractList') Drop View dbo.FactorHeaderAndDetailAbstractList
GO
CREATE TABLE dbo.Tmp_Part
	(
	Serial bigint NOT NULL,
	Code bigint NOT NULL,
	Name varchar(200) NULL,
	ENName varchar(200) NULL,
	StorageRef bigint NULL,
	GroupRef bigint NULL,
	UnitRef bigint NULL,
	OEM varchar(25) NULL,
	Barcode nchar(200) NULL,
	BarcodeImg varbinary(MAX) NULL,
	PartImg varbinary(MAX) NULL,
	AddedTaxInBuy bit NULL,
	AddedTaxInSales bit NULL,
	Active bit NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	EditorRef int NULL,
	EditDate char(10) NULL,
	PartKindRef smallint NULL,
	Reserve float(53) NULL,
	Remain float(53) NULL,
	Orders float(53) NULL,
	MaxRemain bigint NULL,
	MinRemain bigint NULL,
	ShelfNo varchar(15) NULL,
	PartBuyPriceKindRef smallint NULL,
	BuyUnitPrice bigint NULL,
	BuyUnitPriceInForeignCurrency money NULL,
	SaleDependToBuy bit NULL,
	PartProfitPercentKindRef smallint NULL,
	PartSalePriceScopeRef smallint NULL,
	ForeignCurrencyForBuy bit NULL,
	ForeignCurrencyForSale bit NULL,
	Comment varchar(300) NULL,
	OfficeDetailRef int NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Part SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Part)
	 EXEC('INSERT INTO dbo.Tmp_Part (Serial, Code, Name, StorageRef, GroupRef, UnitRef, OEM, Active, RecorderRef, RecordDate, EditorRef, EditDate, PartKindRef, Reserve, Remain, Orders, MaxRemain, MinRemain, ShelfNo, PartBuyPriceKindRef, BuyUnitPrice, BuyUnitPriceInForeignCurrency, SaleDependToBuy, PartProfitPercentKindRef, PartSalePriceScopeRef, ForeignCurrencyForBuy, ForeignCurrencyForSale, Comment, OfficeDetailRef)
		SELECT Serial, Code, Name, StorageRef, GroupRef, UnitRef, OEM, Active, RecorderRef, RecordDate, EditorRef, EditDate, PartKindRef, Reserve, Remain, Orders, MaxRemain, MinRemain, ShelfNo, PartBuyPriceKindRef, BuyUnitPrice, BuyUnitPriceInForeignCurrency, SaleDependToBuy, PartProfitPercentKindRef, PartSalePriceScopeRef, ForeignCurrencyForBuy, ForeignCurrencyForSale, Comment, OfficeDetailRef FROM dbo.Part WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.PartGroupsBridge
	DROP CONSTRAINT FK_PartGroupsBridge_Part
GO
ALTER TABLE dbo.PartSalePrice
	DROP CONSTRAINT FK_PartSalePrice_Part
GO
ALTER TABLE dbo.PartProductRawMaterialHeader
	DROP CONSTRAINT FK_PartProductRawMaterialHeader_Part
GO
ALTER TABLE dbo.PartProductRawMaterialDetail
	DROP CONSTRAINT FK_PartProductRawMaterial_Part
GO
ALTER TABLE dbo.SystemAccCodeDefaultPerPart
	DROP CONSTRAINT FK_SystemAccCodeDefaultPerPart_Part
GO
ALTER TABLE dbo.FactorDetail
	DROP CONSTRAINT FK_FactorsDetail_Parts
GO
ALTER TABLE dbo.StorageCountDetail
	DROP CONSTRAINT FK_StorageCountDetail_Part
GO
ALTER TABLE dbo.PartWarehouseBridge
	DROP CONSTRAINT FK_PartWarehouseBridge_Part
GO
DROP TABLE dbo.Part
GO
EXECUTE sp_rename N'dbo.Tmp_Part', N'Part', 'OBJECT' 
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	PK_Part PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IDX_Part_Remain ON dbo.Part
	(
	Remain
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_Part_OfficeDetailRef ON dbo.Part
	(
	OfficeDetailRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Part_PartProfitPercentKind FOREIGN KEY
	(
	PartProfitPercentKindRef
	) REFERENCES dbo.PartProfitPercentKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Part_PartSalePriceScope FOREIGN KEY
	(
	PartSalePriceScopeRef
	) REFERENCES dbo.PartSalePriceScope
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_Groups FOREIGN KEY
	(
	GroupRef
	) REFERENCES dbo.Groups
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_PartKind FOREIGN KEY
	(
	PartKindRef
	) REFERENCES dbo.PartKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_PartPriceKind FOREIGN KEY
	(
	PartBuyPriceKindRef
	) REFERENCES dbo.PartBuyPriceKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_Storages FOREIGN KEY
	(
	StorageRef
	) REFERENCES dbo.Storages
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Part ADD CONSTRAINT
	FK_Parts_Unit FOREIGN KEY
	(
	UnitRef
	) REFERENCES dbo.Units
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartWarehouseBridge ADD CONSTRAINT
	FK_PartWarehouseBridge_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartWarehouseBridge SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.StorageCountDetail ADD CONSTRAINT
	FK_StorageCountDetail_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.StorageCountDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactorDetail ADD CONSTRAINT
	FK_FactorsDetail_Parts FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.FactorDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SystemAccCodeDefaultPerPart ADD CONSTRAINT
	FK_SystemAccCodeDefaultPerPart_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.SystemAccCodeDefaultPerPart SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartProductRawMaterialDetail ADD CONSTRAINT
	FK_PartProductRawMaterial_Part FOREIGN KEY
	(
	RawMaterialRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartProductRawMaterialDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartProductRawMaterialHeader ADD CONSTRAINT
	FK_PartProductRawMaterialHeader_Part FOREIGN KEY
	(
	ProductRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartProductRawMaterialHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartSalePrice ADD CONSTRAINT
	FK_PartSalePrice_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartSalePrice SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PartGroupsBridge ADD CONSTRAINT
	FK_PartGroupsBridge_Part FOREIGN KEY
	(
	PartRef
	) REFERENCES dbo.Part
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.PartGroupsBridge SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
