SchemaSpy

Graphical Database Schema Metadata Browser

Recent Donors:
coder90210

Anonymous
tvaline

Do you hate starting on a new project and having to try to figure out someone else's idea of a database? Or are you in QA and the developers expect you to understand all the relationships in their schema? If so then this tool's for you.

SchemaSpy is a Java-based tool (requires Java 1.4 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships. The browsing through relationships can occur though HTML links and/or though the graphical representation of the relationships. It's also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints.

It is free software that is distributed under the terms of the Lesser GNU Public License. Your donations are, however, greatly appreciated.

If you like SchemaSpy then please vote for it on freshmeat, digg it, and tag it on del.icio.us.

SchemaSpy uses the dot executable from Graphviz to generate graphical representations of the table/view relationships. This was initially added for people who see things visually. Now the graphical representation of relationships is a fundamental feature of the tool. Graphvis is not required to view the output generated by SchemaSpy, but the dot program should be in your PATH (not CLASSPATH) when running SchemaSpy or none of the graphs will be generated.

Note that some Linux users have experienced problems with Graphviz version 2.6. Versions 2.6 to 2.8 are preferred, but if they don't work then try 2.2.1. Note that SchemaSpy takes advantage of some 2.6+ features so graphs won't be as well formed when using 2.2.1.

SchemaSpy uses JDBC's database metadata extraction services to gather the majority of its information, but has to make vendor-specific SQL queries to gather some information such as the SQL associated with a view and the details of check constraints. The differences between vendors have been isolated to configuration files and are extremely limited. Almost all of the vendor-specific SQL is optional.

Sample output of the tool is available at here. Note that this was run against an extremely limited schema so it doesn't show the full power of the tool.

Running SchemaSpy

You run SchemaSpy from the command line:

java -jar schemaSpy.jar -t dbType -db dbName [-s schema] -u user [-p password] -o outputDir [-nohtml][-noimplied]

	Parameter	Description
	`-t databaseType`	Type of database (e.g. ora, db2, etc.). Use `-dbhelp` for a list of built-in types. Defaults to `ora`.
*	`-db dbName`	Name of database to connect to
*	`-u user`	Valid database user id with read access
	`-s schema`	Database schema (optional if the same as user or isn't supported by your database)
	`-p password`	Password associated with that user. Defaults to no password.
*	`-o outputDirectory`	Directory to write the generated HTML/graphs to
	`-cp pathToDrivers`	Looks for drivers here before looking in driverPath in [databaseType].properties
	`-desc "Schema description"`	Displays the specified textual description on summary pages. If your description includes an equals sign then escape it with a backslash. For example: -desc "<a href\='http://schemaspy.sourceforge.net'>SchemaSpy</a>".
	`-i tableNamesRegex`	Only include matching tables/views. This is a regular expression that's used to determine which tables/views to include. For example: `-i "(.book.)\|(library.*)"` includes only those tables/views with 'book' in their names or that start with 'library'. You might want to use -desc with this option to describe the subset of tables.
	`-x columnNamesRegex`	Exclude matching columns from relationship analysis to simplify the generated graphs. This is a regular expression that's used to determine which columns to exclude. It must match table name, followed by a dot, followed by column name. For example: `-x "(book.isbn)\|(borrower.address)"` Note that each column name regular expression must be surround by `()`'s and separated from other column names by a `\|`.
	`-ahic`	Allow HTML In Comments. Any HTML embedded in comments normally gets encoded so that it's rendered as text. This option allows it to be rendered as HTML.
	`-cid`	Comments Initially Displayed. Column comments are normally hidden by default. This option displays them by default.
	`-notablecomments`	Don't display table-based comments. for databases like MySQL that stuff unrelated data where comments belong.
	`-noimplied`	Don't include implied foreign key relationships in the generated table details
	`-nohtml`	Only generate files needed for insertion/deletion of data (e.g. for scripts)

* denotes required parameter.

Here are the currently supported database types. Use java -jar schemaSpy.jar -dbhelp for a complete list of the supported database types and the parameters that each one requires.

Type	Description
db2	IBM DB2 with the 'App' Driver
db2net	IBM DB2 with the 'Net' Driver
firebird	Firebird
hsqldb	HSQLDB Server
mssql	Microsoft SQL Server
mysql	MySQL
ora	Oracle with OCI8 Driver
orathin	Oracle with Thin Driver
pgsql	PostgreSQL
sybase	Sybase Server with JDBC3 Driver
sybase2	Sybase Server with JDBC2 Driver
udbt4	DB2 UDB Type 4 Driver

A MySQL example:

java -jar schemaSpy.jar -t mysql -o library -host localhost -db library -u user -p password -notablecomments

will create a series of files in the library directory that give the details of the schema in the database library. This is what I used to generate the sample output.

An MS SQL Server example:

java -jar schemaSpy.jar -t mssql -db library -host localhost -port 1433 -u user -p password -o library

does the same thing as the MySQL example, but specifies an mssql database type with MS SQL Server-specific database connection parameters.

Some information about the developer, John Currier, is available here.
Feedback on problems and/or enhancements is appreciated.