One page with all my questions from all sites: open, closed, answered

Question

Can we have one page with all questions I asked, all answers I wrote, and filters for open, closed, answered etc?! It's too much to track all sites!

Answer 1

You probably have to wait for another 6-8 weeks before this feature request gets implemented.

If you can live with the fact that The StackExchange Data Explorer is only refreshed once a week you can use this query to get all your posts across the network. The query requires an accountid and that one can be found by visiting your network account on StackExchange.com and then take the id from the url. Yours is 9348, mine is 281857.

---- accountid: Account on stackexchange.com! "In the url on stackexchange.com"

-- create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO

create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #siteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
go

declare @accountid int = ##accountid:int##  -- 93484

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_posts ( site nvarchar(250)
                            , id int
                            , title nvarchar(200)
                            , posttypeid tinyint
                            , closeddate datetime);
                            
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
               declare @url nvarchar(250)
               exec #siteurl ''' + @db_c_name  + ''', @url output
               insert into #all_posts 
               select @url
               , p.id
               , case 
                 when posttypeid =1 then p.title 
                 when posttypeid =2 then (select title 
                                          from posts q 
                                          where q.id = p.parentid)
                 end
               , p.posttypeid  
               , p.closeddate 
               from posts p 
               inner join users u on u.id = p.owneruserid 
               where u.accountid = '+ cast(@accountid as nvarchar) + ';'
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select replace(site,'.StackExchange','') as [site]
     , 'http://' 
     + site 
     + '.com/'
     + Case 
       when posttypeid = 1 then 'Q' 
       when posttypeid = 2 then 'A' 
       end
     + '/'
     + cast(id as nvarchar)
     + '|'
     + title
     , Case 
       when posttypeid = 1 then 'Q' 
       when posttypeid = 2 then 'A' 
       end as PostType
     , closeddate
from #all_posts

drop table #all_posts

How does this work

I start with creating a procedure (functions are not allowed) to translate a databasename to a site url. The db's are named StackExchange.Photography.meta and the url for that site is meta.photography.stackexchange.com

A temporary table is going to hold all our data. Then I open a cursor to iterate over all databases (except the system db's). In that loop I create a sql statement to insert your posts in the temp table.

After the loop is done the final select statement produces the result where I buildup the final url to the site and post as well.