SchemaCrawler Database Diagramming
SchemaCrawler generates database diagrams using Graphviz in any of the output formats supported by Graphviz. SchemaCrawler is unique among database diagramming tools in that you do not need to know the table names or column names that you are interested in. All you need to know is what to search for, in the form of a regular expression. You can filter out tables, views, and columns based on regular expressions, using grep functionality. SchemaCrawler has powerful command-line options to match tables, and then find other tables related to the matched ones, whether they are parent or child tables. If your schema changes, you can simply regenerate the diagram, without having to know the exact changes that were made to the schema.
SchemaCrawler relies on Graphviz to generate diagrams. Install Graphviz first, and ensure that it is on the system PATH. If installing Graphviz is not an option for you, use the pure Java Graphviz library. Instructions are provided with the SchemaCrawler download. Currently, the the pure Java Graphviz library is only able to generate PNG and SVG diagrams.
Then you can run SchemaCrawler with the correct command-line options - for example,
-command=schema -outputformat=png -outputfile=graph.png
See the diagram example in the SchemaCrawler examples download. An example of a SchemaCrawler database diagram is below.
Database Diagram Options
SchemaCrawler offers several options to change what you see on the database diagram. Here are a few variations:
- Suppress schema names and foreign key names, using the
- Show only significant columns, such as primary and foreign key columns, and columns that are part of unique indexes. Use the
-infolevel=standard -command=briefcommand-line option.
- Show column ordinals, by setting configuration option
schemacrawler.format.show_ordinal_numbers=truein the configuration file.
- Display columns in alphabetical order, using the
- Grep for columns, and also display outgoing relationships, using
-grepcolumns=.*\\.BOOKS\\..*\\.IDas a command-line option.
- Grep for columns, but only show matching tables, using
-grepcolumns=.*\\.BOOKS\\..*\\.ID -only-matchingas command-line options.
- Do not show cardinality on the diagrams, to avoid clutter. Set configuration option
schemacrawler.graph.show.foreignkey.cardinality=falsein the configuration file.
- Show table row counts on the diagrams, set configuration option
schemacrawler.format.show_row_counts=true -infolevel=maximumon the command-line.
- Do not show catalog and schema colors on the diagrams, set configuration option
- Show a title on the diagram, use
-title "Books and Publishers Schema"on the command-line.
You can decide whether foreign-key names, column ordinal numbers, and schema names are displayed by setting the following properties in the SchemaCrawler configuration file,
schemacrawler.format.show_ordinal_numbers=true schemacrawler.format.hide_foreignkey_names=true schemacrawler.format.show_unqualified_names=true
Table Row Counts
You can how table row counts on the database diagram, by setting the following properties in the SchemaCrawler configuration file,
schemacrawler.config.properties, and using
Graphviz Command-line Options
You can provide additional Graphviz command-line options in one of three ways:
- using the
schemacrawler.graph.graphviz_optsproperty in the SchemaCrawler configuration file,
- by passing in the additional arguments using the
SC_GRAPHVIZ_OPTSJava system property,
- or by setting the
SchemaCrawler does not set the dpi, or resolution of generated graphs. A useful Graphviz command-line option to set is
-Gdpi=300. In the SchemaCrawler configuration file,
schemacrawler.config.properties, this would look like:
SchemaCrawler can generate SVG diagrams embedded in HTML output. To generate this format, run SchemaCrawler with an
-outputformat=htmlx command-line argument. Please edit the SchemaCrawler configuration file,
schemacrawler.config.properties, and comment out or delete the line
- Adobe Acrobat Reader sometimes cannot render PDF files generated by GraphViz. In this case, please use another PDF viewer, such as Foxit.
- To set GraphViz command-line options, edit the SchemaCrawler configuration file,
schemacrawler.config.properties, and edit the line with