Shell Programming and Scripting

#cat 11.sql
 
create table abc (
.
.
.
) engine=Innodb ;
.
.
etc
.
.
.
create table UsM (
 blah
blah
blah
) engine=Innodb ;
.
.
.
drop procedure if exists b77 ;
Delimiter $$
CREATE PROCEDURE b77(id int)
BEGIN
END
$$
Delimiter ;
.
.
.
drop procedure if exists a77;
Delimiter $$
CREATE PROCEDURE a77(id int)
BEGIN
END
$$
Delimiter ;
.
.
CREATE PROCEDURE gNodes (LEVEL INT)
BEGIN
 blah
blah
.
.
        SET rootName = (SELECT DirectoryCategory.name FROM DirectoryCategory WHERE DirectoryCategory.id IN (SELECT DirectoryTree.directoryCategoryId FROM DirectoryTree WHERE DirectoryTree.id=rootId)) ;
    
        DROP TABLE IF EXISTS ResultNodes ;
        CREATE TABLE ResultNodes (id INT, NAME TEXT) ;
  END IF ;
  
  IF LEVEL>0 THEN
        SET parentPrefix = CONCAT(pa etc etc
.
.
END
$$
Delimiter ;

perl -lne '(/^\s*create\s+table/i .. /^\s*\)\s+engine=Innodb/i) && print;' "11.sql" > onlytables.sql

perl -e 'print reverse <>' "11.sql"|perl -lne '(/\s+engine=Innodb/i .. /create\s+table/i) && print;'|perl -e 'print reverse <>'

# awk variant..
awk '/create table|engine/{++i}i{x=x"\n"$0}i==2{print x;x=i=""}' inputfile

perl -e '@a=reverse <>;foreach (@a){push (@b,$_) if /\s+engine=Innodb/i .. /create\s+table/i};print reverse(@b),"\n"' "11.sql"