In a lot of data projects, there are times when you need to create some documentation or generate a change log for data discovery. I was provided with 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. What I want to do was create a summary of observations in a word document with the tables listed to add notes to. Something that 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 is quick and dirty. The following steps will walk you through the 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.)
- Change the “Results to File” and save as HTML, or just run the “Results to Text” to see the results during in the Results tab.
- Load the HTML file into a web browser.
4. Copy the page to a Microsoft Word File. I set the layout to landscape, but if your table is narrow, you could keep the file layout as portrait.
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.