SET XACT_ABORT OFF
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
DECLARE @ret int
SELECT @ret = 0

BEGIN TRANSACTION
   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'ALTER TABLE dbo.Main DROP CONSTRAINT fk_Main_Lookup'

   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'ALTER TABLE dbo.Lookup SET (LOCK_ESCALATION = TABLE)'

   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'CREATE TABLE dbo.Tmp_Main (
   KeyCol      int NOT NULL,
   LookupId    int NOT NULL,
   Constrained int NULL,
   DataCol     int NULL
   )  ON [PRIMARY]'

   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'ALTER TABLE dbo.Tmp_Main SET (LOCK_ESCALATION = TABLE)'

   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'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 @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'ALTER TABLE dbo.Sub DROP CONSTRAINT fk_Sub_Main'

   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'DROP TABLE dbo.Main'

   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'EXECUTE  sp_rename N''dbo.Tmp_Main'', N''Main'', ''OBJECT'''

   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'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 @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'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 @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'ALTER TABLE dbo.Main ADD CONSTRAINT
   ck_constrained CHECK (([Constrained]>(0)))'

   IF @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'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 @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'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 @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'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 @ret = 0 EXEC @ret = slog.cmd_catchhandler_sp
'ALTER TABLE dbo.Sub SET (LOCK_ESCALATION = TABLE)'

IF @ret = 0
   COMMIT TRANSACTION
ELSE
   ROLLBACK TRANSACTION
go