Get list of links to all my questions and answers

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results