0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to bypass life's everyday problems with simple tricks

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250), accountid int,
  displayname nvarchar(40), reputation int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union all'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + ''', accountid, displayname, reputation
from ' + quotename(name) + '.dbo.users
'
from sys.databases
where database_id > 5
  and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

;with allusers as
(
select rank() over (order by sum(reputation) desc) AS 'Rank',
  'https://stackexchange.com/users/' + cast(accountid as nvarchar)
  + '|' + max(displayname) AS 'User', accountid as 'Uid',
  max(displayname) AS 'Username',
  sum(reputation) AS 'Total reputation'
  , min(accountid) accountid, ROW_NUMBER() over (order by sum(reputation) desc) as Id
from #results
group by accountid
)

-- show results
select 'All users'
     , [Id], [Rank], [User], [Uid], [Username], [Total reputation]
from allusers
where [Uid] > ##uid## and [Total reputation] > 0
order by 1 desc, [Uid] asc
-- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID; choose if you don't want to focus on a user.

drop table #results

fork query

edited dec 29 21 anonymous

created aug 2 18 Glorfindel