split a sql file into per-table

split_sql.sh

#!/bin/bash

# Splitting a sql file containing a whole database into per-table files is quite easy:
#  - Grep the .sql for any occurence of DROP TABLE.
#  - Generate the file name from the table name that is included in the DROP TABLE statement.
#  - Echo the output to a file.
# by Marcus Pauli

file=$1 # the input file
directory="$file-splitted" # the output directory
output="$directory/__header" # the first file containing the header
GREPTABLE="DROP TABLE" # the string we are looking for to find a new table
GREPDB="Current Database: " # the string we are looking for to find a new database

mkdir $directory # create the output directory
while read line
do
  # if the current line contains the wanted DB
  if [ $(echo "$line" | grep -c "$GREPDB") == "1" ]
    then
    # extract database name
    mydb=$(echo $line | awk '{print $4}' | sed -e 's/`//g')
    # create db directory
    mkdir $directory/$mydb
    # set the new header file name
    output="$directory/$mydb/__header"
  elif [ $(echo "$line" | grep -c "$GREPTABLE") == "1" ]
  then
    # extract the file name
    myfile=$(echo $line | awk '{print $5}' | sed -e 's/`//g' -e 's/;//g')
    # set the new file name
    if [ -z "$mydb" ]
    then
      output="$directory/$myfile"
    else
      output="$directory/$mydb/$myfile"
    fi
  fi
  echo "$line" >> $output # write to file
done < $file