SQL Server: Table Schema Documentation Using DB>Doc

Posted December 20, 2016 by Vishwanath Dalvi in Database, SQL Server

feature-sql1-618x350

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.

db_doc_sql_server_documentation_generator_command_switches

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

 

db_doc_sql_server_documentation_generator_windows_auth

For SQL Server Authentication with user ID and password

sqldbdoc "SERVER=ANDRO100\SQLSERVER2014; TRUSTED_CONNECTION=yes; DATABASE=AdventureWorks2014 user id=sa; [email protected];" filename=AdventuresWork2014Doc.htm /y

 

sql_server_auth_db_doc_schema_generation

You can see HTML generated documents in the following formats.

html_document_schema db doc sql server

html_document_example db doc sql server

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.