Shell Programming and Scripting

sed -n '/CREATE[A-Za-z ]*INDEX*/,/COMMIT/p' filename.ddl

$ echo "CREATE INDEX" | gawk '/CREATE([\t ]+UNIQUE)?[\t ]+INDEX/ { print $0 > NR ".txt" ; close(NR ".txt"); }'
$ cat 1.txt
CREATE INDEX
$

-- TABLE001
CREATE UNIQUE INDEX ATABL001
    ON TABLE001
     (COLUMN1 ASC,
      COLUMN2 ASC,
      COLUMN3 ASC,
      COLUMN4 ASC);
COMMIT;

CREATE INDEX BTABL001
    ON TABLE001
     (COLUMN3 ASC,
      COLUMN4 ASC);
COMMIT;

CREATE INDEX CTABL001
    ON TABLE001
     (COLUMN3 ASC);
COMMIT;

-- TABLE002
CREATE UNIQUE INDEX ATABL002
    ON TABLE002
     (COLUMN1 ASC,
      COLUMN2 ASC,);
COMMIT;
...

awk '/^--/{next}/CREATE UNIQUE INDEX|CREATE INDEX/{file=$NF}{print>file".ddl"}' input.ddl

gawk 'BEGIN { FILE=1 }
/CREATE([\t ]+UNIQUE)?[\t ]+INDEX/ { if(W) close(FILE ".txt");  FILE++; W=0 }
{ print $0 > FILE ".txt" ; W=1 }' < infile