Markdown documents are becoming increasingly more popular and relevant with the emergence of notebooks. Markdown is a markup language for creating formatted text. It is widely used in tools for collaboration, tools for creating documentation and notebooks. Formatting is easy to understand, readable, simple to adopt, and agnostic. I can use a markdown document on different platforms (JupyterHub, Databricks, Github, etc.), with different scripting languages (Python, R, Julia, Scala, SQL), and among different operating systems (Linux, Windows, Unix, MacOS, etc.).

Azure Data Studio (ADS) has offered creation of notebooks for a long time and delivers the basic concept of notebooks: executable code and formatted text in the same document. These notebooks can be execute many different script language, since their kernel can be based on R, Python, Powershell, Scala interpreters.

Markdown text in Azure Data Studio

I normally use notebooks in ADS with all the different kernels. Switching between kernels gives me much more flexibility to execute different functions that are available for me in different kernels. This also applies for T-SQL. In the case below, I have created a new notebook, and executed a simple SELECT statement. The notebook gives me back the results, and I can also add the additional Markdown text as a comment to code. When I want to share this document with others, they will receive a complete notebook with code, comments, and text. In this way, they will have a better chance for data exploration, data understanding, and further collaboration with others.

These capabilities with select statements (as shown in second cell) are created, so that people, I will be sharing notebook with, and myself, get most from your result set. I can also sort and, filter the columns without rerunning the SELECT statement. Furthermore, I can export the result set to CSV, JSON or XML is another way to use the dataset with any other tool.

And ADS offers me also the easy way to visualize the results. But in addition, I can also create the markdown formatted text of the table content (or result set), so that the results are persistent, can be shared, copied elsewhere or simply added to documentation. This is shown in third cell, which holds the same result set but the results themselves are created with markdown language.

In this article, I will explain how to create markdown formatted result set.

Implementation of Markdown language

I will walk you through the process of creating the markdown formatted text for the T-SQL table output using a stored procedure. You don't need any additional software and no additional requirements in order to to create markdown text. The script is based on T-SQL and is standardize in such way, that you can use it in multiple platforms.

Creating a test data set

First, I will create a simple database with a sample table. In next step, I will populate this table with couple of sample rows for the demo purpose.

CREATE DATABASE TestMD;
GO
USE TestMD;
CREATE TABLE dbo.TestForMD
( ID INT IDENTITy(1,1)
,Name VARCHAR(100)
,Age INT NOT NULL
,Salary MONEY
,Height DECIMAL(10,2)
,MaritalStatus CHAR(1)
)

INSERT INTO dbo.TestForMD
SELECT 'Tim', 31, 300, 191.2, 1 UNION ALL
SELECT 'Tom', 21, 400, 181.87, 2 UNION ALL
SELECT 'Tam', 51, 500, 176.54, 3

Preparing the data set

The procedure will take as input parameter the table name and not the T-SQL query. For example, I have a long T-SQL query and I want to store the results as markdown text. What I need to do, is to run the T-SQL query and store the results into a persistent table. Procedure will taking this table to export the result as markdown text.

Creating main stored procedure

The process of exporting table rows into markdown format is done through stored procedure. This stored procedure is constructed from three parts:

• get the column names
• get the data types
• add the header and footer to the markdown notebook
• remove unnecessary annotations

-- Select statement
CREATE OR ALTER  PROCEDURE [dbo].[Select2MD]
/*
Author: Tomaz Kastrun
Date: 08.Nov.2021
Description: Turns result set of selected table into Markdown
Usage:
        EXEC dbo.select2MD
                @table_name = 'TestForMD'
               ,@schema_name = 'dbo'
ToDO:
*/
    @table_name VARCHAR(200)
    ,@schema_name VARCHAR(20)
AS 
BEGIN
        SET NOCOUNT ON;
    -- get the columns of the table
        SELECT 
            c.Column_name
            ,c.Ordinal_position
            ,c.is_nullable
            ,c.Data_Type
        
        INTO  #temp
        
        FROM INFORMATION_SCHEMA.TABLES AS  t
        JOIN INFORMATION_SCHEMA.COLUMNS AS c 
        ON t.table_name = c.table_name
        AND t.table_schema = c.table_schema
        AND t.table_Catalog = c.table_Catalog
        WHERE
        t.table_type = 'BASE TABLE'
        AND t.Table_name = @table_name
        AND t.table_schema = @schema_name
            DECLARE @MD NVARCHAR(MAX)
            -- Title
            DECLARE @title NVARCHAR(MAX) = (SELECT '##Result for table: _**' + CAST(@table_name AS NVARCHAR(MAX)) + '**_
            ###SchemaName: _'+CAST(@schema_name AS NVARCHAR(MAX)) +'_')

            -- header |name |name2 |name3 |name4 |name5 |name6 
            DECLARE @header VARCHAR(MAX)
            SELECT @header = COALESCE(@header + '**|**', '') + column_name 
            FROM #temp
            ORDER BY Ordinal_position ASC
            SELECT @header = '|**' + @header + '**|'

            -- delimiter |-- |-- |-- |-- |-- |-- 
            DECLARE @nof_columns INT = (SELECT MAX(Ordinal_position) FROM #temp)
            DECLARE @firstLine NVARCHAR(MAX) = (SELECT  REPLICATE('|---',@nof_columns) + '|')  

            SET @MD = @title +CHAR(10) + @header + CHAR(13) + CHAR(10) + @firstLine  + CHAR(10)
            -- body
            DECLARE @body NVARCHAR(MAX)
            SET @body = 'SELECT
            ''|'' + CAST(' 
            DECLARE @i INT = 1
            WHILE @i <= @nof_columns
            BEGIN
                DECLARE @w VARCHAR(1000) =  (SELECT column_name FROM #temp WHERE Ordinal_position = @i)
                    SET @body = @body + @w + ' AS VARCHAR(MAX))+ ''|'' + CAST( '
                SET @i = @i + 1
            END
            SET @body  = (SELECT SUBSTRING(@body,1, LEN(@body)-8))
            SET @body = @body + ' FROM ' + @table_name

            DECLARE @bodyTable TABLE(MD VARCHAR(MAX))
            INSERT INTO @BodyTable
            EXEC sp_executesql @body

            DECLARE @body2 NVARCHAR(MAX)
            SELECT @body2 = COALESCE(@body2 + ' ', ' ') + MD + CHAR(10) 
            FROM @bodyTable
            SET @MD = @MD + @body2
            --Addint timestamp
            DECLARE @Timestamp VARCHAR(100) = (SELECT GETDATE())
            DECLARE @UserName VARCHAR(100) = (SELECT SUSER_SNAME())
            DECLARE @FootNote VARCHAR(200) = CHAR(10) + 'Created on: ' + @Timestamp + ' by user: ' + @UserName + CHAR(10)
          
            SET @MD = @MD + @FootNote
            SELECT @MD
END;
GO

After the procedure reads all the column names and get the data types, it starts constructing the table elements. This elements is »| -- |« and optionally, I can specify text left or right alignment based on data type. This element would be »|:-- |« for left alignment, respectively.

Procedure will also concatenate the content of the table into a string. At the end of the result, procedure will also add the username and timestamp. In this way, I will always know, when the content of the table was converted into markdown and who is the author.

Working with the results

Executing procedure is straightforward. I must the define the name and schema of the table, where I have previously stored the results of my query.

EXEC dbo.select2MD
    @table_name = 'TestForMD'
   ,@schema_name = 'dbo'

In Azure Data Studio, I can copy and paste the content of the result cell into an empty notebook.

In SSMS, I can do the same. Or I can set the query result to »print to text«, instead of having the results to table.

The output in both cases would be the same. This is the example of markdown formatted query results.

##Result for table: _**TestForMD**_
###SchemaName: _dbo_ 
|**ID**|**Name**|**Age**|**Salary**|**Height**|**MaritalStatus**|
|---|---|---|---|---|---|
|1|Tim|31|300.00|191.20|1|
|2|Tom|21|400.00|181.87|2|
|3|Tam|51|500.00|176.54|3|

In last case, you can also directly store the table content into markdown file with Powershell script.

-- Store to file with Powershell
Invoke-Sqlcmd -ServerInstance "localhost" -Database "TestMD" -Query "EXEC dbo.select2MD @table_name = 'TestForMD' ,@schema_name = 'dbo'" | Export-Csv "d:\md\result2.md" -NoTypeInformation

This way is convenient when you have larger tables.

Conclusion

Creating markdown text for the table content is a great way to represent your data in a notebook. You will also be able to present your data to end-user better, giving better visibility, and adding the query as a code block (SELECT * FROM TestForMD) with the markdown text. With the use of this procedure, you and people you will be sharing the notebooks with, will be able to create a better documentation and understanding of the data.