Links to all my questions?

Question

Is there a way to get hold of a parsable list of links (or if that's not possible, IDs)

• to all my questions

• to all my answers

preferably without using the data dump?

Answer 1

I have created a query on SEDE, which creates a list of parsable links to all your questions and answers on all supported Stack Exchange sites. You just have to input your Stack Exchange Account ID (not User ID) and you can download the links in a CSV file.

How it works

The idea is fairly simple. The query loops through all available databases available on SEDE and fetches any posts created by you, as your Account ID is identical for all databases. It then uses the database name, post type, and post ID to create a link for every post.

Code

declare @AccountId int = ##AccountId:int##

-- Select all valid databases.
select dbid, name
into #dbs
from master..sysdatabases
where name not in ('Data.StackExchange', 'master', 'model', 'msdb', 'tempdb')

-- Loop through databases.
create table #posts (db varchar(40), site varchar(40), PostTypeId int, Id int)
declare @id int, @db varchar(40), @site varchar(50), @sql nvarchar(max),
        @tmp varchar(40), @start int, @end int
while (select count(*) from #dbs) > 0
begin
  select top 1 @id = dbid, @db = name from #dbs

  -- Database name to host url (e.g. StackExchange.Stats.Meta to meta.stats.stackexchange)
  set @tmp = lower(@db)
  set @site = ''
  while len(@tmp) > 0
  begin
    set @start = 0
    while charindex('.', @tmp, @start+1) <> 0
    begin
      set @start = charindex('.', @tmp, @start+1)
    end
    set @site += '.' + substring(@tmp, @start+1, len(@tmp))
    set @tmp = left(@tmp, case @start when 0 then 0 else @start - 1 end)
  end
  
  -- Fetch each post in database by user.
  set @sql = '
    insert into #posts
    select
      ''' + @db + ''' as db,
      substring(''' + @site + ''', 2, len(''' + @site + ''')) as site,
      p.PostTypeId,
      p.Id
    from [' + @db + ']..[Posts] p
    inner join [' + @db + ']..[Users] u 
    on p.OwnerUserId = u.Id and u.AccountId = ' + cast(@AccountId as varchar(20)) + '
    where PostTypeId in (1, 2)
  '
  exec(@sql)
  delete from #dbs where dbid = @id
end

-- Print out list of links.
select 
  'http://' + site + case when db in ('MathOverflow') then '.net/' else '.com/' end +
  case when PostTypeId=1 then 'q/' else 'a/' end +
  cast(Id as varchar(20)) + 
  '/'
from #posts
order by PostTypeId, db