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
BEGIN TRANSACTION
GO


If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_LatestEmployeeRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_LatestEmployeeRef
End
GO

ALTER TABLE PAY.Employee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_LatestCollectorRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_LatestCollectorRef
End
GO

ALTER TABLE AST.Collectors SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_LatestCostCenterRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_LatestCostCenterRef
End
GO

ALTER TABLE dbo.CostCenter SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_LatestLocationRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_LatestLocationRef
End
GO
ALTER TABLE PAY.JobLocation SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_OperationAssetDocRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_OperationAssetDocRef
End
GO

ALTER TABLE AST.AssetDocHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_OfficeDetailRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_OfficeDetailRef
End
GO

ALTER TABLE dbo.OfficeDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_RecorderRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_RecorderRef
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_EditorRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_EditorRef
End
GO

ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_UnitRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_UnitRef
End
GO

ALTER TABLE dbo.Units SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_Asset_AssetTypeRef')
Begin
	ALTER TABLE AST.Asset DROP CONSTRAINT FK_AST_Asset_AssetTypeRef
End
GO

ALTER TABLE AST.AssetType SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE AST.Tmp_Asset
	(
	Serial bigint NOT NULL IDENTITY (1, 1),
	AssetTypeRef int NOT NULL,
	Code varchar(25) NULL,
	FADescription varchar(50) NULL,
	ENDescription varchar(50) NULL,
	OEM varchar(25) NULL,
	UnitRef bigint NULL,
	AssetComment varchar(300) NULL,
	LatestAssetPlate varchar(MAX) NULL,
	LatestLocationRef bigint NULL,
	LatestCollectorRef bigint NULL,
	LatestEmployeeRef bigint NULL,
	LatestCostCenterRef bigint NULL,
	OperationDate char(10) NULL,
	OperationAssetDocRef bigint NULL,
	DepreciationType smallint NULL,
	DepreciationValue decimal(6, 4) NULL,
	Active bit NULL,
	InactiveRate decimal(6, 4) NULL,
	BuyUnitPrice money NULL,
	AccumulatedDepreciation money NULL,
	BookValue money NULL,
	ResidualValue money NULL,
	Barcode nvarchar(200) NULL,
	BarcodeImg varbinary(MAX) NULL,
	AssetImg varbinary(MAX) NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	EditorRef int NULL,
	EditDate char(10) NULL,
	OfficeDetailRef int NULL,
	Tag varchar(50) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE AST.Tmp_Asset SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT AST.Tmp_Asset ON
GO
IF EXISTS(SELECT * FROM AST.Asset)
	 EXEC('INSERT INTO AST.Tmp_Asset (Serial, AssetTypeRef, Code, FADescription, ENDescription, OEM, UnitRef, AssetComment, LatestAssetPlate, LatestLocationRef, LatestCollectorRef, LatestEmployeeRef, LatestCostCenterRef, OperationDate, OperationAssetDocRef, DepreciationType, DepreciationValue, Active, InactiveRate, AccumulatedDepreciation, BookValue, ResidualValue, Barcode, BarcodeImg, AssetImg, RecorderRef, RecordDate, EditorRef, EditDate, OfficeDetailRef, Tag)
		SELECT Serial, AssetTypeRef, Code, FADescription, ENDescription, OEM, UnitRef, AssetComment, LatestAssetPlate, LatestLocationRef, LatestCollectorRef, LatestEmployeeRef, LatestCostCenterRef, OperationDate, OperationAssetDocRef, DepreciationType, DepreciationValue, Active, InactiveRate, AccumulatedDepreciation, BookValue, ResidualValue, Barcode, BarcodeImg, AssetImg, RecorderRef, RecordDate, EditorRef, EditDate, OfficeDetailRef, Tag FROM AST.Asset WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT AST.Tmp_Asset OFF
GO
ALTER TABLE AST.AssetDocDetail DROP CONSTRAINT FK_AST_AssetDocDetail_AssetRef
GO
DROP TABLE AST.Asset
GO
EXECUTE sp_rename N'AST.Tmp_Asset', N'Asset', 'OBJECT' 
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	PK_AST_Asset 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_AST_Asset_AssetTypeRef ON AST.Asset
	(
	AssetTypeRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_AST_Asset_UnitRef ON AST.Asset
	(
	UnitRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_AST_Asset_RecorderRef ON AST.Asset
	(
	RecorderRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_AST_Asset_EditorRef ON AST.Asset
	(
	EditorRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_AST_Asset_OfficeDetailRef ON AST.Asset
	(
	OfficeDetailRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_AssetTypeRef FOREIGN KEY
	(
	AssetTypeRef
	) REFERENCES AST.AssetType
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_UnitRef FOREIGN KEY
	(
	UnitRef
	) REFERENCES dbo.Units
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_RecorderRef FOREIGN KEY
	(
	RecorderRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_EditorRef FOREIGN KEY
	(
	EditorRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_OfficeDetailRef FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_OperationAssetDocRef FOREIGN KEY
	(
	OperationAssetDocRef
	) REFERENCES AST.AssetDocHeader
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_LatestLocationRef FOREIGN KEY
	(
	LatestLocationRef
	) REFERENCES PAY.JobLocation
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_LatestCostCenterRef FOREIGN KEY
	(
	LatestCostCenterRef
	) REFERENCES dbo.CostCenter
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_LatestCollectorRef FOREIGN KEY
	(
	LatestCollectorRef
	) REFERENCES AST.Collectors
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_LatestEmployeeRef FOREIGN KEY
	(
	LatestEmployeeRef
	) REFERENCES PAY.Employee
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_AssetRef FOREIGN KEY
	(
	AssetRef
	) REFERENCES AST.Asset
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
