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;
GOAfter 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.
