How can I find my posts that use “old school” table format?

Question

Now that the Table support feature Feature Preview: Table Support live, I would like to update my posts that still use the workarounds to show tables. Basically posts where I went nuts with dashes, pipe and plus symbols inside a code block to make it look like a table.

How can I find these posts of mine with a data explorer query? I prefer to have one complete list, across all sites, if that is possible.

Answer 1

If we assume most users will have done the ASCII art trickery inside a code block, with things like |-----+----+----| and variants, the following SEDE query will find your posts that have any of the following patterns in their body inside a code block:

• ----
• |--
• --+--
• --|--

Here is the query source:

declare @sql nvarchar(max)

create table #result (site sysname
                    , hostname sysname
                    , id integer
                    , title nvarchar(250)
                    , body nvarchar(max)
                    )

select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', name ,N''' as site
     , ''',hostname ,N'''
     , p.id
     , q.title
     , p.body
from ', quotename(name), N'.dbo.users u
inner join ', quotename(name), N'.dbo.posts p on
  p.owneruserid = u.id
inner join ', quotename(name), N'.dbo.posts q on
  q.id = coalesce(p.parentid, p.id)
where accountid = ##accountid##
'),N' 
union all')
from (select convert(nvarchar(max),name) name
    -- , convert(nvarchar(max),sitename)  sitename
    -- , meta
    -- , domain
     ,  concat( 
        -- based on an idea from Glorfindel 
        -- https://meta.stackexchange.com/a/321181
        (case sitename
        WHEN 'Audio' THEN 'video'
        WHEN 'Beer' THEN 'alcohol'
        WHEN 'CogSci' THEN 'psychology'
        WHEN 'Garage' THEN 'mechanics'
        WHEN 'Health' THEN 'medicalsciences'
        WHEN 'Moderators' THEN 'communitybuilding'
        WHEN 'Photography' THEN 'photo'
        WHEN 'Programmers' THEN 'softwareengineering'
        WHEN 'Vegetarian' THEN 'vegetarianism'
        WHEN 'Writers' THEN 'writing'
        WHEN 'Br' THEN 'pt'
        WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename)
        ELSE case when sitename = domain then null else sitename end
        end +'.')
        , (case 
           when sitename <> 'mathoverflow' then (meta+'.') 
           else null 
           end) 
        , (case 
           when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') 
           else 'net' 
           end)
        ) hostname
from (
select name
, case parsename(name,1) 
  when 'Meta' then parsename(name,2)
  else parsename(name,1) 
  end [sitename]
, case parsename(name,1) 
  when 'Meta' then 'meta'
  else null
  end [meta]
  , coalesce(parsename(name,3), parsename(name,2)) [domain]
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')
) dbs
) dbsall

exec (@sql)



select concat(
          'https://'
        , hostname
        , '/q/'
        , id
        , '|'
        , title) [Post] 
      , value
from #result
cross apply string_split('----,|--,--+--,--|--',',') line  -- patterns comma separted
where body like concat('%<code>%', line.value, '%</code>%')

The query needs your accountid that is found in the url of your network profile. The detected ascii art patterns are near the end, in the string_split function.

When run today this is the result for user einpoklum:

Keep in mind SEDE is updated once a week on Sunday.
Use the wonderful SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.