A simple tool to produce an entity relationship diagram from a SQL file of table definitions. It is fairly primitive, often requiring a touch up of the input file manually, but still quite useful to see dependencies.

It tries to ignore anything in the input it doesn't understand, and is fairly robust for common SQL grammar objects, but may break with SQL extensions and unusual column/key definitions. If this happens, simply make a copy of the input file and strip out whatever it is complaining about until it works. The minimal input grammar is:

Start => CreateTable*

CreateTable => "create"/i Space "table"/i Space TableName Space? "(" ColumnDefList? ")" ";"

ColumnDefList => ColumnDef | ColumnDef "," ColumnDefList
ColumnDef => ColName Space ColType (Space Reference)?

Reference => "references"/i TableName "(" ColName ")"

TableName => Atom
ColName => Atom
ColType => Atom

Space => [[:space:]]+
Atom => [^[:space:]]+ | '([^']|'')+'

The output is in a format suitable for GraphViz's dot tool. To make any use of the tool, you'll need GraphViz.

SQLGraph is written in Java so you'll need a recent JDK to compile it from the source, and at least a recent JRE to run it from the JAR file below.

Here is a sample input file and the output graph:

-- a foo table
create table foo (
        id      int not null primary key auto_increment,
        foo     varchar(255) not null

-- a bar table
create table bar (
        id      int not null primary key auto_increment,
        bar     varchar(255) not null

-- a linking table
create table foo_bar (
        foo_id  int not null references foo(id),
        bar_id  int not null references bar(id)

-- a naked qux table
create table qux (
        id      int not null primary key auto_increment,
        qux     varchar(255) not null

A typical usage might be:

sqlgraph mySchema.sql | dot -Tps | gv -

Download the tar.gz archive here or the executable JAR file here

[Back] © 2002 Alan Yates <alany(at)alanyates(dot)com>

Valid XHTML 1.0! Valid CSS!