SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. See here for more information.
Graphviz is open source graph visualization software. Graph visualization is a way of representing structural information as diagrams of abstract graphs and networks. See here for more information.
jTDS is an open source JDBC driver for Microsoft SQL Server and Sybase databases. See here for more information
We will use SchemaSpy, Graphviz and JTDS to document a SQL server database.
Step 1: Download Graphviz from the following location
Step 2: Open command prompt in Administrator mode. Change to the directory where the Graphviz msi file has been downloaded and run the
msiexec /a graphviz-2.38.msi
command. As of 8/4/2016, the latest version of Graphviz is 2.38. Run through the various steps of the Graphviz installer.
Step 3: Add the Graphviz bin folder path to the “Path” “system variables” in “Environment variables”. The path of the bin folder on my machine was “C:\Program Files (x86)\Graphviz2.38\bin”.
Step 4: Download Schema Spy from the following location. As of 8/4/2016, the latest version is 5.0.0.
Step 5: Download and unzip the latest JTDS drivers for SQL server from the following location. As of 8/4/2016, the latest version is 1.3.1.
Step 6: For the database being documented, make sure that TCP\IP protocol is enabled and the TCP\IP port points to the port name in step no 7 below(i.e 1433 in this case). This can be configured using “SQL server configuration manager” for SQL server database.
Step 7: Restart database server using the services snapin.
Step 8: Run the following command to generate the documentation for the database.
java -jar schemaSpy_5.0.0.jar -t mssql05-jtds -db SqlTuning -host localhost -port 1433 -u sa1 -p sa1 -o SqlTuningDocumentation -dp C:\Downloads\jtds-1.3.1-dist\jtds-1.3.1.jar -s dbo -loglevel fine