Markdown is a lightweight markup language with plain-text-formatting syntax. It’s is often used for formatting simple readme files, for writing messages in online discussion forums, and to create rich text using a plain text editor. You can read more on this link: https://daringfireball.net/projects/markdown/.
Sometimes I need simple reports from my SQL Server queries and put it on some wiki pages, SharePoint portals, GitHub documentation pages or similar. Markdown simple document format is very suitable for this kind of purposes. By focusing more on the content I can still apply minimalistic formatting on the document.
In the next section, I will show how I can create simple Markdown reports from T-SQL queries.
Commonly, SQL queries return tabular data structures. Therefore, I will show how to wrap SQL query inside Markdown table definition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
SET NOCOUNT ON -- ON removes "rows affected" status text on each query PRINT '# My system report, generated at ' + CAST(GETDATE() AS VARCHAR) PRINT '## System info ' SELECT '| Hostname | Sql Server Edition | Sql Server version | Machine name |' UNION ALL SELECT '|---|---|---|---|' UNION ALL SELECT '| ' + HOST_NAME() + ' |' + CONVERT(VARCHAR(40),SERVERPROPERTY('Edition')) + '|' + CONVERT(VARCHAR(40),SERVERPROPERTY('ProductVersion')) + ' | ' + CONVERT(VARCHAR(10), SERVERPROPERTY('MachineName')) PRINT '## Sessions' SELECT '| Spid | Login name | Hostname | Name | Cmd | Wait Time | Status |' UNION ALL SELECT '|---|---|---|---|---|---|---|' UNION ALL SELECT '| ' + CONVERT(VARCHAR(10), sp.spid) + ' |' + RTRIM(sp.loginame) + ' |' + RTRIM(sp.hostname) + ' |' + d.name+ ' |' + sp.cmd+ ' |' + CONVERT(VARCHAR(10),sp.waittime)+ ' |' + RTRIM(sp.status)+ ' |' FROM sys.sysprocesses AS sp, sys.databases AS d WHERE sp.dbid=d.database_id; PRINT '## Databases' SELECT '| Databases on this server | Server version |' UNION ALL SELECT '|---|---|' UNION ALL SELECT '| ' + name + ' |' + CASE compatibility_level WHEN 60 THEN ' 60 (SQL Server 6.0)| ' WHEN 65 THEN ' 65 (SQL Server 6.5)| ' WHEN 70 THEN ' 70 (SQL Server 7.0)| ' WHEN 80 THEN ' 80 (SQL Server 2000)| ' WHEN 90 THEN ' 90 (SQL Server 2005)| ' WHEN 100 THEN ' 100 (SQL Server 2008)| ' WHEN 110 THEN ' 110 (SQL Server 2012)| ' WHEN 120 THEN ' 120 (SQL Server 2014)| ' WHEN 130 THEN ' 130 (SQL Server 2016)| ' WHEN 140 THEN ' 140 (SQL Server 2017)| ' WHEN 150 THEN ' 150 (SQL Server 2019)| ' END FROM sys.databases |
Some tips and tricks
Before extracting the data from the SQL Server and present it as a markdown document, I just need to do some little tricks with T-SQL or on SSMS query options.
The most common tool for any SQL Developer is SQL Server Management Studio IDE (SSMS). There are, of course, also other ways how to get the data from SQL Server – but as I said – SSMS is the most commonly used tool for handling SQL data manipulation or administration.
Let’s take a look on these tricks.
Result to Text
For better experience, I recommend to display data as a text output. You do this in Query options in SSMS by hitting Ctrl+T or via menu Query/Results To/Results To Text.
Set NOCOUNT on
To exclude “rows affected” text at the end of every query result I need to SET NOCOUNT OFF” in my T-SQL, e.g.
Disable displaying “Show completion time” option
If I want to rid of “Completion time” text at the end of the query processing I need to uncheck my query option “Show completion time“, e.g.
Disable “Include column headers in the result set” option
To remove header separator in SSMS Text output I need to uncheck “Include column headers in the result set“, e.g.
It would be really awesome, if I could control all these output parameters via T-SQL, but I guess this is not possible.
If someone has some ideas how this can be done, please put comments below this blog post.
Anyway, when everything is set, I can execute my query and I get this output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
# My system report, generated at Nov 23 2020 2:14PM ## System info | Hostname | Sql Server Edition | Sql Server version | Machine name | |---|---|---|---| | ARTHUR |Developer Edition (64-bit)|15.0.2070.41 | ARTHUR ## Sessions | Spid | Login name | Hostname | Name | Cmd | Wait Time | Status | |---|---|---|---|---|---|---| | 9 |sa | |master |SIGNAL HANDLER |2325156 |background | | 13 |sa | |master |BRKR TASK |2325167 |background | | 15 |sa | |master |TASK MANAGER |0 |sleeping | | 16 |sa | |master |TASK MANAGER |0 |sleeping | | 17 |sa | |master |TASK MANAGER |0 |sleeping | | 18 |sa | |master |TASK MANAGER |0 |sleeping | | 19 |sa | |master |TRACE QUEUE TASK |613 |background | | 22 |sa | |master |BRKR EVENT HNDLR |2325157 |background | | 23 |sa | |master |CHECKPOINT |2171036 |background | | 24 |sa | |master |LOG POOL MEMORY NOTIFICATI |254 |background | | 26 |sa | |master |BRKR TASK |254 |background | | 27 |sa | |master |BRKR TASK |45 |background | | 28 |sa | |master |BRKR TASK |2325168 |background | | 29 |sa | |master |TASK MANAGER |0 |sleeping | | 30 |sa | |master |TASK MANAGER |0 |sleeping | | 31 |sa | |master |TASK MANAGER |0 |sleeping | | 32 |sa | |master |TASK MANAGER |0 |sleeping | | 33 |sa | |master |TASK MANAGER |0 |sleeping | | 34 |sa | |master |TASK MANAGER |0 |sleeping | | 35 |sa | |master |TASK MANAGER |0 |sleeping | | 36 |sa | |master |TASK MANAGER |0 |sleeping | | 37 |sa | |master |TASK MANAGER |0 |sleeping | | 38 |sa | |master |TASK MANAGER |0 |sleeping | | 39 |sa | |master |TASK MANAGER |0 |sleeping | | 40 |sa | |master |TASK MANAGER |0 |sleeping | | 41 |sa | |master |TASK MANAGER |0 |sleeping | | 42 |sa | |master |TASK MANAGER |0 |sleeping | | 43 |sa | |master |TASK MANAGER |0 |sleeping | | 44 |sa | |master |TASK MANAGER |0 |sleeping | | 45 |sa | |master |TASK MANAGER |0 |sleeping | | 46 |sa | |master |TASK MANAGER |0 |sleeping | | 47 |sa | |master |TASK MANAGER |0 |sleeping | | 48 |sa | |master |TASK MANAGER |0 |sleeping | | 49 |sa | |master |TASK MANAGER |0 |sleeping | | 50 |sa | |master |TASK MANAGER |0 |sleeping | | 51 |ARTHUR\joze |ARTHUR |master |AWAITING COMMAND |0 |sleeping | | 52 |sa | |master |TASK MANAGER |0 |sleeping | | 53 |sa | |master |TASK MANAGER |0 |sleeping | | 54 |ARTHUR\joze |ARTHUR |master |AWAITING COMMAND |0 |sleeping | | 55 |ARTHUR\joze |ARTHUR |master |AWAITING COMMAND |0 |sleeping | | 56 |NT SERVICE\SQLSERVERAGENT |ARTHUR |msdb |AWAITING COMMAND |0 |sleeping | | 57 |NT SERVICE\SQLSERVERAGENT |ARTHUR |msdb |AWAITING COMMAND |0 |sleeping | | 58 |ARTHUR\joze |ARTHUR |master |AWAITING COMMAND |0 |sleeping | | 60 |ARTHUR\joze |ARTHUR |BikeStores22 |SELECT |0 |runnable | ## Databases | Databases on this server | Server version | |---|---| | master | 150 (SQL Server 2019)| | tempdb | 150 (SQL Server 2019)| | model | 150 (SQL Server 2019)| | msdb | 150 (SQL Server 2019)| | BikeStores | 150 (SQL Server 2019)| | test1 | 150 (SQL Server 2019)| | BikeStores2 | 150 (SQL Server 2019)| | BikeStores123 | 150 (SQL Server 2019)| | BikeStores22 | 150 (SQL Server 2019)| | MyDatabase | 130 (SQL Server 2016)| |
My Markdown report is ready. I can upload it to some portal which can consume and display Markdown document. For example, I will show how my MD document looks in GitHub preview.
Nice, right?
Summary
In this blog post, I showed how to wrap T-SQL queries to generate simple Markdown reports.
Especially tabular forms are very interesting here, because this kind of structures are the most common ones.
If you find this approach useful, or you have any comment, please put some comments bellow.
Happy coding!