How can I find all the StackExchange websites that I'm part of?

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/questions/324806/how-can-i-find-all-the-stackexchange-websites-that-im-part-of-queries-on-dat#324826

Stack Overflow

Q&A for professional and enthusiast programmers

declare @sql nvarchar(max)

create table #result (site sysname
                    , displayname nvarchar(150)
                    , id integer
                    , upvotes integer
                    , downvotes integer
                    , q integer
                    , a integer)

select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', convert(nvarchar(max),name) ,N''' as site
     , u.displayname
     , u.id
     , u.upvotes
     , u.downvotes
     , sum(case p.posttypeid when 1 then 1 else 0 end) Q
     , sum(case p.posttypeid when 2 then 1 else 0 end) A
from ', convert(nvarchar(max),quotename(name)), N'.dbo.users u
left outer join ', convert(nvarchar(max),quotename(name)), N'.dbo.posts p on
  p.owneruserid = u.id
where accountid = ##accountid##
group by u.displayname, u.id, u.upvotes, u.downvotes
'),N' 
union all')
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')

exec (@sql)

select *
from #result

select (select count(*)
from #result) [sites with account]
, (select count(*)
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')) [total sites]

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results