Use TSQL to Generate SQL Server Schema Documentation

In many data projects, there are times when you need to create documentation or generate a changelog for data discovery. I had a client with 16 tables with 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 to the teams.

I wanted to generate data schema documentation quickly during a data discovery project. I created a TSQL hack to create formatted documentation in HTML documentation using the SQL database schema. This same pattern can be used for other solutions, so I thought I would share how I solved this and the code sample.

The format needed to be a summary of observations in a word document with the tables and schema listed and the ability to add notes. One of the key features is that it needed to cut down the great deal of time requited to generate a document for the client in order to get their feedback.

In This Article:

• What is a SQL Schema?
• Documentation Tools too Costly for Simple One-offs
• Using TSQL to Generate HTML
• A VSCode Project with Builtin Web Server

What is a SQL Schema

A SQL schema is a logical way of organizing and managing database objects. It consists of various items, such as tables, views, indexes, etc., belonging to a specific database user with the rights to create, modify, and delete them.

                                                            SQL Server Schema of an Address Table

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

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

Use TSQL to Generate HTML

Putting my developer hat on, I accomplished a project back in the day that created HTML out of text output. What about using TSQL to generate HTML from the 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 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 a 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 in 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 that 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 who 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. :).