/* 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 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
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
ALTER TABLE dbo.Main
	DROP CONSTRAINT fk_Main_Lookup
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
ALTER TABLE dbo.Lookup SET (LOCK_ESCALATION = TABLE)
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
CREATE TABLE dbo.Tmp_Main
	(
	KeyCol int NOT NULL,
	LookupId int NOT NULL,
	Constrained int NULL,
	DataCol int NULL
	)  ON [PRIMARY]
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
ALTER TABLE dbo.Tmp_Main SET (LOCK_ESCALATION = TABLE)
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
IF EXISTS(SELECT * FROM dbo.Main)
	 EXEC('INSERT INTO dbo.Tmp_Main (KeyCol, LookupId, Constrained, DataCol)
		SELECT KeyCol, LookupId, Constrained, CONVERT(int, DataCol) FROM dbo.Main WITH (HOLDLOCK TABLOCKX)')
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
ALTER TABLE dbo.Sub
	DROP CONSTRAINT fk_Sub_Main
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
DROP TABLE dbo.Main
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
EXECUTE sp_rename N'dbo.Tmp_Main', N'Main', 'OBJECT'
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
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]

GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
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]
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
ALTER TABLE dbo.Main ADD CONSTRAINT
	ck_constrained CHECK (([Constrained]>(0)))
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
ALTER TABLE dbo.Main ADD CONSTRAINT
	fk_Main_Lookup FOREIGN KEY
	(
	LookupId
	) REFERENCES dbo.Lookup
	(
	LookupId
	) ON UPDATE  NO ACTION
	 ON DELETE  NO ACTION

GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
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
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
ALTER TABLE dbo.Sub ADD CONSTRAINT
	fk_Sub_Main FOREIGN KEY
	(
	KeyCol
	) REFERENCES dbo.Main
	(
	KeyCol
	) ON UPDATE  NO ACTION
	 ON DELETE  NO ACTION

GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
ALTER TABLE dbo.Sub SET (LOCK_ESCALATION = TABLE)
GO
IF @@error <> 0 OR @@trancount = 0
   RAISERROR('Error occurred or transaction aborted', 16, 127)
GO
COMMIT