What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 131 Stack Exchange communities.

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?

share|improve this question
    
isn't this something the api can do? stackapps.com –  Jeff Atwood Feb 2 '11 at 11:42
    
@Jeff I'm looking for a end-user tool for one-time use. Maybe stackapps has something, I'll check it out, thanks –  Pëkka Feb 2 '11 at 11:45
    
@Arjan thanks, just discovered that myself, that'll work great! –  Pëkka Feb 2 '11 at 11:51
    
Not parsable, but the default search queries would be (1) user:me is:question and (2) user:me is:answer. –  Werner Aug 27 at 5:37
    
@Arjan Your links are dead. Is there anywhere else this is available now? –  Banana Aug 27 at 16:58

1 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
share|improve this answer

You must log in to answer this question.

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