7

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!

  • 3
    You mean across the whole network? We already have such a thing: stackexchange.com/users/93484/sprbrn – Shadow The Princess Wizard Jan 27 '15 at 16:36
  • 2
    That does not show all questions and answers though, only those with score 5 or higher it seems. – HugoRune Jan 27 '15 at 17:28
  • 1
    @HugoRune If you look at the "top" tab then yes. You can go to activity and then sort to posts to view every single question and answer you've created. – animuson Jan 27 '15 at 17:48
  • 3
    That view seems to show only the last 100 questions and answers. It is impossible to go back further than 5 pages á 20 entries, or to sort the list by some criteria other than "most recent". – HugoRune Jan 27 '15 at 17:57
11

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.

  • 1
    I'm amazed that we can write something like this ourselves. Thanks! And do you mean that a function like this is going to be implemented in two months or so? I've searched for this functionality, but didn't get any useful results. – SPRBRN Jan 28 '15 at 8:39
  • 2
    @SPRBRN one of the meme's of SE, 6 to 8 weeks – rene Jan 28 '15 at 9:59
  • @rene lol - do you have that typo on all your SEDE queries!? – ᔕᖺᘎᕊ Jul 29 '15 at 13:46
  • 1
    Yes, @ᔕᖺᘎᕊ I'm afraid I have but thanks to you I find and fix them now ;) – rene Jul 29 '15 at 13:51

You must log in to answer this question.

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