3

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/119575/how-can-i-view-all-my-unaccepted-questions-across-se

Q&A for those who study, teach, research and apply economics and econometrics

---- 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 url based on dbname
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      if @dbname = 'StackExchange.Meta' 
      begin
        set @res = 'Meta.StackExchange'
      end
      else
      begin
        declare @p1 nvarchar(250) = parsename(@dbname,1)
        declare @p2 nvarchar(250) = parsename(@dbname,2)
        declare @p3 nvarchar(250) = parsename(@dbname,3)
        
        set @res = 
          (case when @p1 = 'Meta' then @p2 else @p1 end) + 
          (case when @p2 is null then '' else '.' end) +
          (case when @p1 = 'Meta' then @p1 else isnull(@p2,'') end) +
          (case when @p3 is null then '' else '.' end) +
          isnull(@p3,'')     
      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_questions ( site nvarchar(250)
                            , title nvarchar(250)
                            , qid int);
                            
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
declare @userid int = null
select @userid = id 
from users where accountid =  '+ cast(@accountid as nvarchar) + '
if @userid is not null 
begin
  insert into #all_questions
  select @url
       , title
       , id
  from posts p
  where owneruserid = @userid
  and p.posttypeid = 1 --Q
  and answercount>0
  and acceptedAnswerid is null
end;'
               
    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/'
     + 'q'
     + '/'
     + cast(qid as nvarchar)
     + '|'
     + title
from #all_questions
order by 1 asc

drop table #all_questions

fork query

edited jul 17 17

created jun 30 15 rene