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 object_id ('tempdb..#exec') IS NOT NULL
   DROP PROCEDURE #exec
go
CREATE PROCEDURE #exec @sql nvarchar(MAX) AS
  PRINT @sql
  EXEC (@sql)
  IF @@error <> 0
  BEGIN
     IF @@trancount > 0 ROLLBACK TRANSACTION
     SELECT 1/0
  END
go
BEGIN TRANSACTION
EXEC #exec N'ALTER TABLE dbo.Main DROP CONSTRAINT fk_Main_Lookup'

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

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

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

EXEC #exec N'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 #exec N'ALTER TABLE dbo.Sub DROP CONSTRAINT fk_Sub_Main'

EXEC #exec N'DROP TABLE dbo.Main'

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

EXEC #exec N'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 #exec N'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 #exec N'ALTER TABLE dbo.Main ADD CONSTRAINT
   ck_constrained CHECK (([Constrained]>(0)))'

EXEC #exec N'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 #exec N'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 #exec N'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 #exec N'ALTER TABLE dbo.Sub SET (LOCK_ESCALATION = TABLE)'

COMMIT
go