perl prg.pl .

#!/usr/bin/perl -w

$prg =  "abaxx.pl" ;

die  "Usage: perl $prg <StartDir>" 
  if(scalar(@ARGV) <  1 );

$tbl_tbsp =  "TS_TBL_MID" ;
$ind_tbsp =  "TS_IND_MID" ;


&recurse ($ARGV[ 0 ]);

#&AddTableSpaceClause ( $ARGV[ 0 ] );

sub recurse {
    my $dir = shift;
    opendir(DIR, $dir) || die  "can't opendir $dir: $!";
    my @files = readdir(DIR);
    closedir DIR;
    foreach ( @files ) {
        next if ( $_ eq '..' or $_ eq '.' );
#        print $_;
        my $file = "$dir/$_";
        if ( -d $file ) {
           &recurse ( $file );
        }
        if ( -f $file && m/\.sql$/i ) {
	   print "Processing: $file\n";
           &AddTableSpaceClause ( $file );
        }
    }
}

sub AddTableSpaceClause {
  my ($fname) = @_;

  my ($tmp_file) = "#tmp#.tmp";
  my ($delim) = $/;

  undef $/;

  open (SRC, "<$fname") || die("Can't open $fname" );
  open (TMP,  ">$tmp_file" ) || die( "Can't open $tmp_file" );

  while (<SRC>) {
    s#(CONSTRAINT\s+\w+\s+((UNIQUE)|(PRIMARY\s+KEY))\s+.*?\))#$ 1  USING INDEX TABLESPACE $ind_tbsp#ismg;
    s#(^CREATE\s+TABLE\s+\w+\s+\(.*?)([\/\;])$#$ 1  TABLESPACE $tbl_tbsp\n$ 2 \n#ismg;
    s#(^CREATE\s+TABLE\s+\w+\s+)(AS\s+.*?)([\/\;])$#$ 1  TABLESPACE $tbl_tbsp $ 2 \n$ 3 \n#ismg;
    s#(^CREATE\s+UNIQUE\s+INDEX\s+.*?)([\/\;])$#$ 1  TABLESPACE $ind_tbsp\n$ 2 \n#ismg;
    s#(^CREATE\s+PRIMARY\s+KEY\s+INDEX\s+.*?)([\/\;])$#$ 1  TABLESPACE $ind_tbsp\n$ 2 \n#ismg;
    s#(^CREATE\s+INDEX\s+.*?)([\/\;])$#$ 1  TABLESPACE $ind_tbsp\n$ 2 \n#ismg;
    print TMP  "$_" ;
  }

  close (TMP);
  close (SRC);

  rename $tmp_file, $fname;

  $/ = $delim;
}