When you are "study"-ing (for whatever reason) someone else's database, and the database has more than 20 tables, you are in trouble to understand what goes where.
Now, imagine a database with 300+ tables. It's like spaghetti, but not enjoyable. Rather, horrific.
I faced a similar challenge recently with a database of 250+ tables. Yes, i felt like in a deep sh*t. And, started looking for tools which can describe the tables in at least decent ER-diagram. If anything more, better. And, obviously free as my company is not paying.
Then, I found SchemaSpy, originally authored by John Currier. It generate a complete in-depth HTML-based description (of course, including clickable ER-diagram) of the database, which you can then browse with your browser. This post is on it's basic usage.
The output will be kind of like this:
It is based on Java technologies, but can work its magic on most of the major database technologies. But, you would need a appropriate JDBC connector for that database.
Quote from the author:
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.
In this post, as example, i have shown to use it with PostgreSQL. But, it's not the only one supported. You can use ot with any proper RDBMS system as long as it has a JDBC-connector. Now, to use it, you need these staffs.
- First of all, your system should have Java runtime properly installed. Download from here.
- SchemaSpy, which is a .jar file. Get it here. At the time of writing, it was version 5.0.0.
- JDBC connector to PostgreSQL. Make sure to match your PostgreSQL version. You can download it from here.
You can check your PostgreSQL version by executing:
SELECT version();query on psql prompt.
- Also, SchemaSpy depends on GarphViz to generate the ER-diagrams, so you need to be installed it on your system. Get it from here.(http://www.graphviz.org/Download..php)
- And, of course make sure PostgreSQL instance running & serving the database you are trying to visualize.
Quoting from the author:
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.
Graphviz 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 entity relationship diagrams will be generated (or use the
For convenience, I kept the .jar files (JDBC-connector, and SchemaSpy) in my home folder.
Now, in my case, my OS is Linux, and database is hosted locally, hence address is
PostgreSQL-9.3 at port
5432. So, i run the command like this:
$ java -jar ./schemaSpy_5.0.0.jar -t pgsql -host 127.0.0.1:5432 -db your_database_name \ -u your_DB_user_name -p your_password -s public \ -dp ./postgresql-9.3-1102.jdbc3.jar \ -o output_folder
It may take a little while, depending on the size of the schema of the database.
After that, you will find the output folder/directory named
You'll see some output when the magic is going on, similar to this below.
Using database properties: [./schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties Gathering schema details....................(6sec) Writing/graphing summary....................(2sec) Writing/diagramming detail..................(31sec) Wrote relationship details of 113 tables/views to directory 'output' in 41 seconds. View the results by opening output_folder/index.html
Now, all the generated files are in the
output_folder. Start your journey by starting from the
index.html in the output folder. Open it by using any browser you want.
Good luck. :)