http://meta.stackexchange.com/a/279137/158100
Q&A for Drupal developers and administrators
-- start 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 -- we have three sites that have borked DNS entries set @dbname = (case @dbname when 'StackExchange.Audio' then 'StackExchange.Avp' when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta' when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta' else @dbname end) -- and one of those doesn't want to end with .com if @dbname <> 'net.Mathoverflow.meta' set @dbname = 'com.' + @dbname exec #recursesiteurl @dbname, @res OUTPUT end GO -- create url from dbname IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL DROP PROCEDURE #recursesiteurl; GO create procedure #recursesiteurl @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 #recursesiteurl @rec, @rec output set @res = @rec + '.' + substring(@dbname, 0, @pos) end end; GO -- all databases declare db_c cursor for select [name] from sys.databases where database_id > 5 -- skip master, temp, model, msdb, Data.SE and ([name] not like '%.Meta' or [Name] = 'StackExchange.Meta') 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_users ( site nvarchar(250) , userid int , rep int , accountid int , displayname nvarchar(250)); create index ix_all_users on #all_users( accountid); 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_users select @url , id , reputation , accountid , displayname from users where reputation > 1;' exec (@sql) fetch next from db_c into @db_c_name end; close db_c; deallocate db_c; ;with max_user_site as ( select row_number() over(order by sum(rep) desc) as rank , accountid , sum(rep) as tot_rep from #all_users group by accountid ), max_site_for_user as ( select userid , accountid , displayname , site , rep from #all_users au where rep = (select max(rep) from #all_users where accountid = au.accountid) ), max_user_site_excl_main as ( select row_number() over(order by sum(rep) desc) as rank , accountid , sum(rep) as tot_rep from #all_users au where rep < (select max(rep) from #all_users where accountid = au.accountid) group by accountid ) select mus.rank , replace( replace( replace(msu.site, '.StackExchange.com',''), '.com',''), '.net','') as [site] , 'http://' + msu.site + '/users/' + cast(msu.userid as nvarchar) + '|' + msu.displayname as [user] , mus.tot_rep as [total rep] , replace( replace( replace(msu2.site, '.StackExchange.com',''), '.com',''), '.net','') as [site] , 'http://' + msu2.site + '/users/' + cast(msu2.userid as nvarchar) + '|' + msu2.displayname as [user] , msuem.tot_rep as [rep minus main] from max_user_site mus inner join max_site_for_user msu on msu.accountid = mus.accountid inner join max_user_site_excl_main msuem on mus.rank = msuem.rank inner join max_site_for_user msu2 on msu2.accountid = msuem.accountid order by mus.rank drop table #all_users drop procedure #recursesiteurl drop procedure #siteurl