Every developer likes to work on a code that is thoroughly documented with documentation that makes sense and provides adequate insight and information to understand the code’s functionality. Most programming languages provide special tools to generate documentation. For example, the JavaDoc tool is used for generating API documentation in HTML format from available doc comments in Java programs. Alas, Microsoft SQL Server does not include any method to generate table schema documentation. We need to rely on third-party tools to document table schema in SQL Server.
DB>doc for Microsoft SQL Server is a tool adapted to generate table schema documentation. This tool uses SQL Server’s system tables which store information for table structures, views, and constraints to generate schema documentation. Documentation generated using DB>doc provides a list of tables classified by different schema names which makes it easy to be used as a reference. It employs SQL server’s Extended Properties which developers use to document database objects.
DB>doc for Microsoft SQL Server generates documentation in numerous formats including HTML, WikiPlex and XML. The most favored document format is HTML. It generates a single-page HTML document with a list of tables assorted by their schema name and also provides hyperlinks to foreign keys for identifying relationships between two tables.
I have tested DB>doc tool with Microsoft SQL Server 2008, 2008 R2, 2012 and 2014 editions. It is a Windows command line application. You will need to download the Exe file format from DB>doc project’s CodePlex page. The direct link to download the tool is DB>doc for Microsoft SQL Server.
Use DB>doc for Microsoft SQL Server to Generate HTML Documentation for Table Schema
1. Navigate to the folder where you have downloaded the “sqldbdoc.exe” application file.
2. Go to the address bar, and type “cmd” to open the current path in command explorer.
3. Type “sqldbdoc.exe /?” to see all permissible parameters.
Important Parameter information
1. connection – Connection string, Database server IP address, instance name, username, password and database name
2. file name – Output file name
3. [/y] – This switch will be used to overwrite the existing generated file name.
Generating an HTML document
You can follow the example below to generate an HTML document.
For Windows Authentication
sqldbdoc "SERVER=ANDRO100\SQLSERVER2014;TRUSTED_CONNECTION=yes;DATABASE=AdventureWorks2014" filename=AdventuresWork2014Doc.htm /y
For SQL Server Authentication with user ID and password
sqldbdoc "SERVER=ANDRO100\SQLSERVER2014; TRUSTED_CONNECTION=yes; DATABASE=AdventureWorks2014 user id=sa; password=test@2016;" filename=AdventuresWork2014Doc.htm /y
You can see HTML generated documents in the following formats.
This tutorial should help you generate and maintain table schema documentation for Microsoft SQL Server. You can go through the Tech-Recipes SQL Server archives tutorials for more information.
