SET XACT_ABORT ON
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
GO
BEGIN TRANSACTION
EXEC ('ALTER TABLE dbo.Main DROP CONSTRAINT fk_Main_Lookup')
IF @@error <> 0 GOTO BitterEnd

EXEC ('ALTER TABLE dbo.Lookup SET (LOCK_ESCALATION = TABLE)')
IF @@error <> 0 GOTO BitterEnd

EXEC('CREATE TABLE dbo.Tmp_Main
	(
	KeyCol int NOT NULL,
	LookupId int NOT NULL,
	Constrained int NULL,
	DataCol int NULL
	)  ON [PRIMARY]')
IF @@error <> 0 GOTO BitterEnd

EXEC('ALTER TABLE dbo.Tmp_Main SET (LOCK_ESCALATION = TABLE)')
IF @@error <> 0 GOTO BitterEnd

EXEC ('IF EXISTS(SELECT * FROM dbo.Main)
	 INSERT INTO dbo.Tmp_Main (KeyCol, LookupId, Constrained, DataCol)
		SELECT KeyCol, LookupId, Constrained, CONVERT(int, DataCol)
      FROM dbo.Main WITH (HOLDLOCK TABLOCKX)')
IF @@error <> 0 GOTO BitterEnd

EXEC('ALTER TABLE dbo.Sub DROP CONSTRAINT fk_Sub_Main')
IF @@error <> 0 GOTO BitterEnd

EXEC('DROP TABLE dbo.Main')
IF @@error <> 0 GOTO BitterEnd

EXECUTE  sp_rename N'dbo.Tmp_Main', N'Main', 'OBJECT'
IF @@error <> 0 GOTO BitterEnd

EXEC('ALTER TABLE dbo.Main ADD CONSTRAINT pk_Main PRIMARY KEY CLUSTERED
	(KeyCol
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]')
IF @@error <> 0 GOTO BitterEnd

EXEC('CREATE NONCLUSTERED INDEX LookupId_ix ON dbo.Main(LookupId)
     WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]')
IF @@error <> 0 GOTO BitterEnd

EXEC('ALTER TABLE dbo.Main ADD CONSTRAINT
	ck_constrained CHECK (([Constrained]>(0)))')
IF @@error <> 0 GOTO BitterEnd

EXEC('ALTER TABLE dbo.Main ADD CONSTRAINT fk_Main_Lookup 
     FOREIGN KEY (LookupId) REFERENCES dbo.Lookup(LookupId) 
     ON UPDATE  NO ACTION
	 ON DELETE  NO ACTION')
IF @@error <> 0 GOTO BitterEnd

EXEC ('CREATE TRIGGER Audit_Main ON dbo.Main FOR INSERT, UPDATE AS
   INSERT Audit(TableName, KeyValue1, OtherCols)
      SELECT ''Main'', ltrim(str(KeyCol)),
             (SELECT LookupId, Constrained, DataCol
              FOR    XML RAW(''Data''))
      FROM   inserted')
IF @@error <> 0 GOTO BitterEnd

EXEC('ALTER TABLE dbo.Sub ADD CONSTRAINT
	fk_Sub_Main FOREIGN KEY (KeyCol) 
      REFERENCES dbo.Main(KeyCol) 
      ON UPDATE  NO ACTION
	 ON DELETE  NO ACTION')
IF @@error <> 0 GOTO BitterEnd

EXEC('ALTER TABLE dbo.Sub SET (LOCK_ESCALATION = TABLE)')
IF @@error <> 0 GOTO BitterEnd

COMMIT
BitterEnd:
IF @@trancount > 0 ROLLBACK TRANSACTION