As a technical person who works with a relational database every day, there are needs for me to know the database schemas. I need a graphical schema to show relationships of tables as a reference which I can review from time to time. And when a new product or a new version of an existing product is released, I would like to browse the database schemas. Fortunately there is a great tool named SchemaSpy that enables me to do so. It can generate graphical relationships of tables for database schemas.

It was written by John Currier initially, but the development stopped with the version 5.0.0 in August 2010. You can find it here:

http://schemaspy.sourceforge.net

The download link:

https://sourceforge.net/projects/schemaspy/files/schemaspy

Sample output:

http://schemaspy.sourceforge.net/sample/

Later the development has been picked up by a group of developers who love the tool. They put the tool here:

http://schemaspy.org/

https://github.com/schemaspy/schemaspy

The current version is 6.1 released in late 2019. The documentation can be found here:

https://schemaspy.readthedocs.io/

Both tools (the initial one and latest community one) are java based command line tools and the tool itself is just a jar file. Joachim Uhl has developed a GUI — SchemaSpyGUI for the initial one.

And both tools need Graphviz to generate graphical representations of the table/view relationships. Starting with the version 6.1, the community one can use embedded viz.js (option “-vizjs“) to generate graphical outputs. Therefore no need to install Graphviz separately with 6.1.

So which tools to use, the latest community one or the original one? Since the original one was developed 10 years ago, an intuitive thought is to use the latest community one which has richer features, a modern and nicer look of html outputs. While that is true, the original one has its benefits: the size of the output is much smaller although the look seems ugly; it doesn’t generate output for Oracle internal schemas with “-all” option; And the most important thing is it meets the basic requirement.

Anyhow, here are the common things to get both tools running on a Windows machine for an Oracle database:

1. Latest Java runtime environment (e.g JRE 1.8) installed
2. Graphviz installed — e.g. 2.38 stable release.
3. Oracle Instant Client unzipped to a directory e.g “C:\Tools\instantclient_19_6”. Version 19.6.0.0.9 needs Microsoft Visual Studio 2017 Redistributable and the x64 version doesn’t support Windows 7.
4. Windows system environment variable PATH contains both Graphviz and Oracle Instant Client path
   • C:\Program Files (x86)\Graphviz2.38\bin
   • C:\Tools\instantclient_19_6
5. Under the Oracle Instant Client directory, create the directory “network/admin“, then create the file tnsnames.ora with the connection to your Oracle database.

Then download the tool (one jar file), if you put them under c:\Tools, here are commands to run them:

Original one — schemaspy_5.0.0.jar

C:\Tools>java -jar schemaspy-6.1.0.jar -t ora -db USER -host 10.28.39.120 -port 1521 -u test -p test_pass -o c:\Tools\UserSchema -hq -dp C:\Tools\instantclient_19_6 -all -cat %

Community one — schemaspy-6.1.0.jar

C:\Tools>java -jar schemaspy-6.1.0.jar -t ora -db USER -host 10.28.39.120 -port 1521 -u test -p test_pass -o c:\Tools\UserSchema -hq -dp C:\Tools\instantclient_19_6 -all -cat %

• -t specify database type, “ora” means oracle — defualt option without “-t”
• -dp specify Oracle Instant Client directory. Note it needs OCI, so just specify OJDBC thin driver doesn’t work.
• -o output directory
• -hq high quality images
• -u user who can visit other schemas
• -p password for the user specified by “-u”
• -db the connection name defined in tnsnames.ora
• -all analyze all schemas. The original one won’t check Oracle internal schemas
• -cat % needed for the community one only. % is the wildcard.

• Oracle