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 totalled 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 to. It would not take a great deal of time to generate to provide to the client and get their feedback.
Documentation Tools are Costly for Simple One-offs
There are many tools that you can purchase to generate documentation, however, also costs money or requires an install on client machines or servers. I wanted something fast which would not leave a foot print 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 out of results of a ‘select from INFORMATION_SCHEMA.COLUMNS’. The code below is a T-SQL script that generates an HTML document out of 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.
- 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))
- Change the “Results to File” and save it as HTML or just run the “Results to Text” to see the Results tab results.
- 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 portrait rather than landscape if your table is narrow.
5. Depending on the size of 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. :).