Schema Spy: A Tool for Visualising and Analysing Database Schemas
Do you want to understand and visualise the schema of a complex salesforce org? Are there hundreds of objects, thousands of fields and many relationships in your org that you can’t visualise with the native Schema Builder? If you answered yes to any of these questions, then I highly recommend using this cool utility tool: Schema Spy.
More often than not, enterprise-scale projects are required to produce documents that can describe the underlying database structure to either design a new system or optimise the existing one for compliance requirements etc. It can make your life easier by providing a more detailed view of your database.
What is Schema Spy?
Schema Spy is a Java-based command line tool that analyses the metadata of a schema in a database and generates ER diagrams. It lets you click through the hierarchy of database tables via child and parent table relationships. It supports many types of databases like Oracle, IBM, MySQL, Microsoft SQL server etc.
It uses the dot executable from Graphviz to generate graphical representations of the table relationships. The graphical representation of relationships is a fundamental feature of the tool.
Installation:
Folders and files: Below are a few of the files you will need to use the tool. Refer to the download links.
- Apache Ant: https://ant.apache.org/bindownload.cgi
- Salesforce migration tool: https://developer.salesforce.com/page/Force.com_Migration_Tool
- Schemaspy files: https://sourceforge.net/projects/schemaspy/files/
- Graphviz: http://www.graphviz.org/Download.php
Configuration:
Below are some of the files that you will need to update in order to configure the tool. Mostly, configuration is for username, password + security token, connection url etc.
build.xml:
force.properties
Ensure that, password is appended with security token and connProps (in build.xml) is set to the correct url based on the org production or sandbox.
Execution:
Execute below command in the terminal.
- export ANT_HOME=/Users/Salesforce/run-schemaspy/apache-ant-1.9.7
- export PATH=$PATH:$ANT_HOME/bin
- echo ‘export PATH=$PATH:/Users/Salesforce/run-schemaspy/apache-ant-1.9.7/bin’ >> ~/.profile
- echo ‘export CLASSPATH=$CLASSPATH:~> > /Users/Salesforce/run-schemaspy/apache-ant-1.9.7/lib:~/Users/Salesforce/run-schemaspy/’ >> ~/.profile
- ant
That’s it! Now you have a folder named – doc which includes the result. Open index.html file.
Results:
As you can see, Schema Spy is quite easy to configure and will change the way you look at your database.
