Visualize your Database Schema

Analyzing database metadata or schema can be troublesome.

Though there are tools available in products such as Oracle (Object Browser),

and MS SQL Server (Enterprise Manager), such utilities are limited in what they

let you do and you may have to grind for hours to understand the schema.

SchemaSpy is a free schema



analyzing tool built in Java.  The tool provides a graphical representation

of all the tables, constraints, views, and provides a diagram of relationships

that can be viewed on any Web browser. The tool also lists the anomalies present

in the schema so that you can either refine or normalize it.

Setup



You need to install JRE on your machine before you run SchemaSpy. It

generates graphs for the schema using 'dot.exe' executable of 'Graphviz'

— an open source graphing utility. You need to install this  as well from

http://www.graphviz.org/Download.php. You can simply copy the executable JAR

file for SchemaSpy to one of the drives to end the setup. The common syntax for

running this utility is:

java —jar

schemaspy_.jar -

Visual representation generated in HTML by SchemaSpy helps in viewing implied relations (dotted lines) also in the schema

We used version 2.1.2 — the latest on offer and tried it

out with databases created on Oracle 10g Express Edition, MySQL 5.0, MS SQL

Server 2000 SP4, and IBM DB2 v 8.2.  The platform used was Windows 2003

server and JRE version 1.5 Update 6 while for Graphviz we used version 2.8.

Using with Oracle 10g



For running SchemaSpy with Oracle 10g, you have to include the JAR file

provided by Oracle in the 'classpath' or you can simply point to their

location using the '-cp' command line option while executing SchemaSpy. For

versions 1.4 and above, the 'ojdbc14.jar' is to be used for connecting with

Oracle over a thin driver. This JAR file is located in

'\app\oracle\ product\10.2\ server\jdbc\lib' directory

where '' represents the location of Oracle

installation's root folder. Following is the sample command we executed to

view a schema named 'HR' in Oracle using the 'thin' drivers:

java —jar

schemaspy_2.1.2.jar —cp c:\oraclexe\app\oracle\ product\10.2\server\jdbc\lib\ojdbc14.jar

—t orathin —db XE —s HR —host localhost —port 1521 —o c:\oracle_demo

—u —p

The syntax while using 'thick' drivers is same except

for option '-t' whose value will be 'oci8' and a different jar file

(oci8.jar).

Command-line switches

Though the command is similar for all databases, the switch that specifies database type carries different values to indicate the type of database. We found that it is not necessary to use the same set of switches with all the databases. You can omit a few switches in some databases as the program assumes default values.

Using with MySQL 5.0



As with Oracle, here too, you need JDBC drivers to be on your system for

making SchemaSpy communicate with MySQL database. Downloading 'mysql-connector-java-

from the url http://dev.mysql.com/downloads/connector/j/ does this. We used

version 3.1.12 available at the url http://dev.mysql.com/

downloads/connector/j/3.1.html for our trial run. Once you have downloaded the

package and extracted its contents you can simply include

mysql-connector-java-3.1.12-bin.jar in your classpath. Following is the sample

command to analyze the schema of a sample 'mysql_demo' database we created

for using with SchemaSpy.

java —jar

schemaspy_2.1.2.jar —cp

c:\mysql-connector-java-3.1.12\mysql-connector-java-3.1.12-bin.jar —t mysql

—db mysql_demo —host localhost —o c:\mysql_demo —u

—p

The command does not require port number to be specifically

mentioned in MySQL, as it takes up the default port number '3306'. However,

you need to specify the host or you will get an error message asking for missing

host information.

Using with DB2 v8.2



In case of DB2, you have to be careful about the case of  the database

and schema name. DB2 is case-sensitive for these and takes upper cases for all

entities i.e. the database name, schema name and the tables and their

attributes. The command we used for DB2 was:

java —jar

schemaspy_2.1.2.jar —cp c:\progra~1\ibm\ sqllib\ java\db2java.zip —t db2

—db GGN —s GGN_SCH —host localhost —port 50000 —o c:\db2_demo —u

—p

In this case, the JDBC drivers required are located in the

java folder of 'SQLLIB' of the database installation. The path for this is

'\program files\sqllib\java\' and the file name is

'db2java.zip'. A point to be noted here is that if the value for your

classpath switch (-cp) contains any white spaces, such as 'Program Files' in

our case, you will have to go with the DOS name, so we used 'progra~1' in

this case. The port number as seen is '50000', which is the default port for

DB2 server. However, you can skip the host and port information here as

SchemaSpy takes up the default value.

Using with MS SQL Server 2000 SP4



For SQL Server the JDBC drivers are provided in a separate package, which

needs to be downloaded from the URL http://www.microsoft.com/downloads/Browse.aspx?displaylang=en&categoryid=3

and installed before you can proceed with SchemaSpy. For SQL Server, you don't

need to specify the JAR files in —cp options once you have installed the JDBC

drivers. The default port for SQL server is 1433. The command we used for SQL

server was:

java —jar

schemaspy_2.1.2.jar —t mssql —db demo_sql  —host localhost —port

1433 —o c:\demo —u —p

The utility creates html files in the output directory and

categorizes the summary into 'Tables', 'Relationships', 'Utility

Tables', 'Constraints' and 'Anomalies', and 'Columns' .You can

browse through these and view related information by choosing the options

provided in some of the sections. For example, the Column section contains an

option 'Related Columns', which also displays columns related to a given

column.  Thus, you can navigate through the database schema, viewing even

minutest details easily.

Anadi Misra