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 TRY

   BEGIN TRANSACTION
   EXEC ('ALTER TABLE dbo.Main DROP CONSTRAINT fk_Main_Lookup')

   EXEC ('ALTER TABLE dbo.Lookup SET (LOCK_ESCALATION = TABLE)')

   EXEC('CREATE TABLE dbo.Tmp_Main
      (
      KeyCol int NOT NULL,
      LookupId int NOT NULL,
      Constrained int NULL,
      DataCol int NULL
      )  ON [PRIMARY]')

   EXEC('ALTER TABLE dbo.Tmp_Main SET (LOCK_ESCALATION = TABLE)')

   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)')

   EXEC('ALTER TABLE dbo.Sub DROP CONSTRAINT fk_Sub_Main')

   EXEC('DROP TABLE dbo.Main')

   EXECUTE  sp_rename N'dbo.Tmp_Main', N'Main', 'OBJECT'

   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]')

   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]')

   EXEC('ALTER TABLE dbo.Main ADD CONSTRAINT
      ck_constrained CHECK (([Constrained]>(0)))')

   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')

   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')

   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')

   EXEC('ALTER TABLE dbo.Sub SET (LOCK_ESCALATION = TABLE)')

   COMMIT
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH