Are you new to a database schema and want to get to know it quickly?  It is very common for a system in development to interact with an existing system and all too often, the requirements for this interaction are vague and difficult to understand, and gaining knowledge about this (potentially very old) system can be even harder.

Of course, there are tons of ways to learn about a schema.  Old school DBA’s may prefer to do manual queries on the views of the database metadata (all_tables, user_tables, all_objects, etc) to get an idea of how it works.  People not as familiar with such ‘DBA-esque’ things may have a hard time extracting an understanding of the data model using nothing but queries.  Often times, it might be easier to get an understanding using such tools as Toad and SQuirreL.  I just recently found a tool called SchemaSpy that can answer a LOT of questions that a ‘newbie’ may have about a schema.

Conveniently open-sourced, running this program will create a local 'website' that can show the user almost anything they want to know.  For ease of reading, here’s a bulleted list that outlines its behavior:

• List of all tables, row counts, and number of children and parents
• A complete graphical mapping of all tables and the relationships between them
• Possible anomalies (tables without indexes, columns that are nullable but have 'must be unique' constraints, etc)
• A list of all columns and in what tables they reside
• Views that are available and the code that produces them

I find it to be one of the most useful tools I’ve seen in terms of pointing out things in minutes that would take much more time to interpret with nothing but the results of data queries.

A few things to know – it is compatible with many different kinds of databases, but you may or may not need to use a JDBC driver of your own.  In order to have the graphics created by SchemaSpy, you’ll also need to install Graphviz (also free, and also blogged about by Matt Harrah) and make sure that dot.exe is in your path.  After that, it just takes a few minutes to learn the parameters, and a single command line call to run the .jar file, and you’re good to go!

I've attached part of an example from it, but check out SchemaSpy's website - their example in particular - to learn more. http://schemaspy.sourceforge.net/