Now that Markdown tables are a thing, can we please have an option in the Stack Exchange Data Explorer to export the data in a format that's ready to paste in a Stack Exchange post?

I mean, I can do ugly things like this, and check the 'Text-only results' option:

enter image description here

which produces

But it shouldn't be too difficult to add another export option to make our lives easier.

1 Answer 1


It will take some effort to implement this, specially if you also want to magic links to work.

Until this is done I'll offer this small helper query to produce a basic table quickly. I took your example query and fiddled a bit with the information_schema and its Columns table. That will give for any table the columns. I used that to build up an union statement, similar to yours but without having to define the columns, it uses the columns in a result table.

In my example I used a #temp table and therefor I query the tempdb.information_schema. But if you only have a simple projection over an existing table you can use the information_schema from the current database.

Adding or renaming a column in the #result table is now automatically reflected in the generated table.

Here is the example query

-- build a #result table

select top 5
     , owneruserid
     , creationdate
     , score
     , viewcount as [my col name]
into #result  -- temp table
from posts
order by id

-- generate  markdown table 

declare @sql nvarchar(max)

select @sql = 'select ''|' + string_agg(column_name, '|') + '|'' as tbl' 
+  ' union all select ''|' + string_agg('-'        , '|') + '|'' as hdr'
+  ' union all select concat(''|'',' 
                           + string_agg(quotename(column_name), ',''|'',') 
                                                          + ',''|'') as row
     from #result' -- temp table
from tempdb.information_schema.columns -- in tempdb
where table_name like '#result%'       -- our temp table name

-- print @sql
exec (@sql)

And here is what the table looks like it generates:

id owneruserid creationdate score my col name
1 1 Jun 28 2009 7:14AM 65 3213
6 41673 Jun 28 2009 8:40AM 10 629
9 6309 Jun 28 2009 8:58AM 8 293
10 11361 Jun 28 2009 9:16AM 13 712
11 84671 Jun 28 2009 9:19AM 4 279
  • Nice! I never realized that temporary table information is stored in the information schema of the tempdb...
    – Glorfindel Mod
    Feb 10, 2021 at 7:27
  • @Glorfindel it is a bit wonky (notice the LIKE ) but I was remembered y'day about its workings when I read about a possible attack vector. So while I checked if that was exploitable I decided to make myself useful ...
    – rene
    Feb 10, 2021 at 7:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .