Use ATNDBTest
Go


/*
   Friday, August 08, 20146:07:47 PM
   User: 
   Server: .
   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.BankAccount
	DROP CONSTRAINT FK_BankAccount_Users
GO
ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BankAccount
	DROP CONSTRAINT FK_BankAccount_BankAccountKind
GO
ALTER TABLE dbo.BankAccountKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BankAccount
	DROP CONSTRAINT FK_BankAccount_Bank
GO
ALTER TABLE dbo.Bank SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BankAccount
	DROP CONSTRAINT FK_BankAccount_AccEntityL1CodeBook
GO
ALTER TABLE dbo.AccEntityL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_BankAccount
	(
	Serial int NOT NULL,
	Code int NULL,
	BankRef int NULL,
	OffshootCode varchar(20) NULL,
	OffshootName varchar(100) NULL,
	CreationDate char(10) NULL,
	AccountNo varchar(30) NULL,
	MasterName varchar(50) NULL,
	BankAccountKindRef int NULL,
	Address varchar(200) NULL,
	Phone1 varchar(20) NULL,
	Phone2 varchar(20) NULL,
	Fax varchar(20) NULL,
	AccGroupCodeBookRef bigint NULL,
	AccGeneralCodeBookRef bigint NULL,
	AccAdjutantL1CodeBookRef bigint NULL,
	AccEntityL1CodeBookRef bigint NULL,
	AccEntityL2CodeBookRef bigint NULL,
	Active bit NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	Description varchar(300) NULL,
	OfficeDetailRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_BankAccount SET (LOCK_ESCALATION = TABLE)
GO
DECLARE @v sql_variant 
SET @v = N'سریال'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'Serial'
GO
DECLARE @v sql_variant 
SET @v = N'کد'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'Code'
GO
DECLARE @v sql_variant 
SET @v = N'سریال بانکها'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'BankRef'
GO
DECLARE @v sql_variant 
SET @v = N'کد شعبه'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'OffshootCode'
GO
DECLARE @v sql_variant 
SET @v = N'نام شعبه'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'OffshootName'
GO
DECLARE @v sql_variant 
SET @v = N'تاریخ افتتاح حساب'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'CreationDate'
GO
DECLARE @v sql_variant 
SET @v = N'شماره حساب'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'AccountNo'
GO
DECLARE @v sql_variant 
SET @v = N' نام صاحب حساب'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'MasterName'
GO
DECLARE @v sql_variant 
SET @v = N'سریال نوع حساب'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'BankAccountKindRef'
GO
DECLARE @v sql_variant 
SET @v = N'آدرس'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'Address'
GO
DECLARE @v sql_variant 
SET @v = N'تلفن1'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'Phone1'
GO
DECLARE @v sql_variant 
SET @v = N'تلفن2'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'Phone2'
GO
DECLARE @v sql_variant 
SET @v = N'فکس'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'Fax'
GO
DECLARE @v sql_variant 
SET @v = N'سریال تفضیلی'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'AccEntityL1CodeBookRef'
GO
DECLARE @v sql_variant 
SET @v = N'سریال جزء تفضیلی'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'AccEntityL2CodeBookRef'
GO
DECLARE @v sql_variant 
SET @v = N'فعال یا غیر فعال بودن حساب'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_BankAccount', N'COLUMN', N'Active'
GO
IF EXISTS(SELECT * FROM dbo.BankAccount)
	 EXEC('INSERT INTO dbo.Tmp_BankAccount (Serial, Code, BankRef, OffshootCode, OffshootName, CreationDate, AccountNo, MasterName, BankAccountKindRef, Address, Phone1, Phone2, Fax, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, Active, RecorderRef, RecordDate, Description, OfficeDetailRef)
		SELECT Serial, Code, BankRef, OffshootCode, OffshootName, CreationDate, AccountNo, MasterName, BankAccountKindRef, Address, Phone1, Phone2, Fax, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, Active, RecorderRef, RecordDate, Description, OfficeDetailRef FROM dbo.BankAccount WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.CheckBookHeader
	DROP CONSTRAINT FK_CheckBookHeader_BankAccount
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_BankAccount
GO
DROP TABLE dbo.BankAccount
GO
EXECUTE sp_rename N'dbo.Tmp_BankAccount', N'BankAccount', 'OBJECT' 
GO
ALTER TABLE dbo.BankAccount ADD CONSTRAINT
	PK_BankAccount 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.BankAccount ADD CONSTRAINT
	FK_BankAccount_AccEntityL1CodeBook FOREIGN KEY
	(
	AccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.BankAccount ADD CONSTRAINT
	FK_BankAccount_Bank FOREIGN KEY
	(
	BankRef
	) REFERENCES dbo.Bank
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.BankAccount ADD CONSTRAINT
	FK_BankAccount_BankAccountKind FOREIGN KEY
	(
	BankAccountKindRef
	) REFERENCES dbo.BankAccountKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.BankAccount ADD CONSTRAINT
	FK_BankAccount_Users FOREIGN KEY
	(
	RecorderRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_BankAccount FOREIGN KEY
	(
	BankAccountRef
	) REFERENCES dbo.BankAccount
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CheckBookHeader ADD CONSTRAINT
	FK_CheckBookHeader_BankAccount FOREIGN KEY
	(
	BankAccountRef
	) REFERENCES dbo.BankAccount
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CheckBookHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
