Use TSQL to Generate SQL Server Schema Documentation

I solved a big issue I had during a data discovery project by creating HTML documentation to document the SQL database schema by using a TSQL hack. This same pattern can be used for other things, so I thought I would share how I did it and the code.

In many data projects, there are times when you need to create some documentation or generate a changelog for data discovery. I had 16 tables which totaled 16,000 columns of data. The columns were very sparse, and I needed to analyze the data at a high level and provide questions or issues back to the teams.

I want to create a summary of observations in a word document with the tables listed to add notes. It would not take a great deal of time to generate to provide to the client and get their feedback.

What is a SQL Schema

A SQL schema is a way of organizing and managing database objects in a logical manner. A schema consists of various objects, such as tables, views, indexes, etc., that belong to a specific database user who has the rights to create, modify, and delete them.

A schema helps to separate database objects for different purposes, such as security, performance, or application development. A schema also helps to avoid name conflicts among database objects by using a qualified name that includes the schema name and the object name. For example, sales.customers refers to the customers table in the sales schema. A SQL schema is always associated with one database, but a database can have more than one schema.

Documentation Tools are Costly for Simple One-offs

There are many tools that you can purchase to generate documentation; however, they also cost money or requires installation on client machines or servers. I wanted something fast which would not leave a footprint or require weeks of negotiations to get installed. Seeing as you can select table information from the system table INFORMATION_SCHEMA.COLUMNS, all I wanted was a way to get the results into a simple table in Word.

Use TSQL to Generate HTML

Putting my developer hat on, I did a project back in the day that created HTML out of text output. What about using TSQL to generate HTML from results of a ‘select from INFORMATION_SCHEMA.COLUMNS’. The code below is a T-SQL script that generates an HTML document from the schema results. The results are a little rough but are quick and dirty.   The following steps will walk you through how to set it up.

1. Copy the T-SQL script to SQL Server Management Studio. (The script is also available on GitHub with a test harness in VSCode to view the HTML results if you want to modify the code. TSQL_Document: Document SQL Server tables using TSQL to generate an HTML page with Bootstrap 3 to format (github.com))
   
2. Change the “Results to File” and save it as HTML or just run the “Results to Text” to see the Results tab results.
   
3. Load the HTML file into a web browser.

4. Copy the page to a Microsoft Word File. I set the layout to landscape, but you could keep the file layout as a portrait rather than landscape if your table is narrow.

5. Depending on the size of the field names, the table may be stretched. Select the table, and on the menu bar, highlight the Table Tools – Layout – AutoFit Contents toolbar options. This will format the table correctly.

6. The result is a quick and very usable document that you can use as documentation starter or a change log for your data discovery project.

VSCode Project with Web Server

The code referenced in this article can also be found a the GitHub Repository SQL TSQL Document. The code is also part of a VSCode project that you can run to view the HTML page in a browser window I used as a test harness during development. You only need the SQL file, but if you want a simple VSCode .node project which will refresh the browser view each time you run the TSQL, you can find it in the repository with instructions in the Notes.MD on getting the gulp-webserver setup.

The code, having HTML in the TSQL, would not display, so I have included a link to the SQL File here.  A picture of the VSCode project is shown below.

Note: Bootstrap is used to format the table. You can also change the CSS in the code to change the format of the output.

I have found myself going back to this script from time to time to create paper forms for SQL Documentation for those that need something in HTML format for a wiki or some online documentation tool. You can use the same TSQL style for other documentation you might need. An example of there is a hack for everything, LOL. :).