Visualize your Database Schema

By: Anadi Misra  

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
-

src='/IMG/433/17433/schemasummarymay2k6.jpg' width=300 border=0>

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.

src='/IMG/434/17434/tableentmay2k6.jpg' width=500 border=0>

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.

Source: PCQuest