I’ve just got a database dump from Oracle and I wanted to load it into mysql database. It turned out that it has a lot of Oracle specific things, so I decided to play a little with grep and awk to get the things I want.

Let’s suppose that we have a database dump:

CREATE TABLE test1 (
  COL1 INTEGER NOT NULL,
  COL2 INTEGER NOT NULL,
  LAST_UPDATED_ON TIMESTAMP,
  LAST_UPDATED_BY CHAR(8))

INSERT INTO test1 VALUES (d,f)
INSERT INTO test1 VALUES (a,b)
INSERT INTO test1 VALUES (a,b)

CREATE TABLE test2 (
  COL44 INTEGER NOT NULL,
  COL55 INTEGER NOT NULL,
  LAST_UPDATED_ON TIMESTAMP,
  LAST_UPDATED_BY CHAR(8))

which is a typical structure from mysqldump or other databases.

• to get all table names from that dump:

 grep "CREATE TABLE " dump.sql  | cut -d" " -f3 | sort

Result:

test1
test2

• to get all table structures from that file:

 awk 'BEGIN { RS="\n\n"; FS="\n"; PATTERN="CREATE TABLE" } { if ( $0 ~ PATTERN ) print $0; }' dump.sql

Result:

CREATE TABLE test1 (
  COL1 INTEGER NOT NULL,
  COL2 INTEGER NOT NULL,
  LAST_UPDATED_ON TIMESTAMP,
  LAST_UPDATED_BY CHAR(8))
CREATE TABLE test2 (
  COL44 INTEGER NOT NULL,
  COL55 INTEGER NOT NULL,
  LAST_UPDATED_ON TIMESTAMP,
  LAST_UPDATED_BY CHAR(8))