Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation
-- 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
select ''' + name + ''', accountid, displayname, reputation
from ' + quotename(name) + '.dbo.users
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')
-- execute it
;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',
sum(reputation) AS 'Total reputation'
, min(accountid) accountid, ROW_NUMBER() over (order by sum(reputation) desc) as Id
group by accountid
-- show results
select 'All users'
, [Id], [Rank], [User], [Total reputation]
where [Id] > ##Id##
order by 1 desc, [Id] 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