SchemaSpy is a great free open source software for database documentation. You can get a first glimpse of this awesome tool by viewing the sample output. This blog post shows how to get SchemaSpy up and running in a new Windows environment and how to document your SQL Server databases.

1. Download Open JDK (or another Java distribution of your choice)
2. Download SchemaSpy
3. Extract Open JDK zip to a folder of your choice
4. Add that folder to the path environment variable (I chose C:\SQL\schemaSpy\installation\openjdk-20\jdk-20.0.2\bin)
   click OK
5. restart

• Open cmd or powershell and type “java”
  output should be like
  usage: java [Options] <Main class> [args…]

      (to execute a class)

…

If “command not found” is returned or similar error messages adding java to the path didn’t work

• Download jdbc driver at https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16
• Extract and copy mssql-jdbc-12.4.0.jre11.jar to C:\SQL\schemaSpy\installation
• Run schemaspy from the command line (cmd or PowerShell) logging in with SQL username and password

Here’s an example…you will have to change everything in italics to your environment

java -jar C:\SQL\schemaSpy\installation\schemaspy-6.2.4.jar -o C:\sql\schemaSpy\output\ -vizjs -t mssql17 -dp ‘C:\SQL\schemaSpy\installation\mssql-jdbc-12.4.0.jre11.jar‘ -connprops ‘encrypt\=false’ -db StackOverflowDocumentation -host ‘DESKTOP-RGO4OCI‘ -port 1433 -u ‘sa‘ -p ‘P4$$w0rd!‘

1. The parameters are documented here.
   1. Relevant database types for SQL Server (parameter –t) are as of now:
      1. mssql17 for SQL Server 2017 and higher
      1. mssql08 for SQL Server 2008 to 2016
      1. There are a few more for older versions including the jtds driver which has been end of live for quite some time now. You can look at the whole list running schemspy with the parameter –dbhelp.
   1. Caution: The order of the flags does matter. It is vital that the parameter –dp follows directly after parameter –t. Otherwise you’ll get an add error message that a quite old version of mssql-jdbc…jar was not found.
   1. The newer SQL Server JDBC drivers do use encryption by default. This is probably what you would like to use in your prod environment. However as I am running SQL Server on Docker and didn’t want to get confused about encryption in a demo environment I added “encrypt\=false” using the connprops parameter.
   1. Please don’t use the sa account unless you are running a local demo environment with uncritical data as I am.

The output of a successful run will look like this:

You can also run SchemaSpy with integrated authentication using your AD account however that requires some additional setup…so I will look into that in a separate blog post.