Let's say I'm interested in having the Enthusiast badge for every community I belong to. How would I go about seeing which communities lack the Enthusiast badge without visiting every community and checking the badges?
You can use the following query to determine on which sites you have not yet earned a specific badge.
You have to provide a badgename and an accountid (you'll find that in the url of your networkprofile).
declare @accountid int = ##accountid?7246920## declare @badgename nvarchar(50) = ##badgename:string?enthusiast## -- result table, don't rename and keep the site column create table #results ( site nvarchar(250) , badgename nvarchar(250) , userid 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' , 'insert into #results' ) + -- here goes the per site query, fully qualify the database objects N' select ''' + name + ''' , ( select name from ' + quotename(name) + '.dbo.badges b where b.userid = u.id and b.name = ''' + @badgename + ''' collate sql_latin1_general_cp1_ci_ai ) as bname , u.id from ' + quotename(name) + '.dbo.users u where u.accountid = ' + cast(@accountid as nvarchar) + ' ' from sys.databases where database_id > 5 -- and (name not like '%.Meta' or name = 'StackExchange.Meta') print @sql -- execute it exec (@sql) -- show results select -- URL builder from Lamak http://stackoverflow.com/a/38129701 'http://' + case PARSENAME(site,1) when 'audio' then 'avp' else PARSENAME(site,1) end + coalesce('.' + case PARSENAME(site,2) when 'audio' then 'avp' else PARSENAME(site,2) end , '') + IIF(patindex('%.MathOverflow%',site) > 0 , '' , coalesce('.' + PARSENAME(site,3), '') ) + IIF(patindex('%.MathOverflow%',site) > 0 , '.net' , '.com' ) + '/badges/' + '|' + replace(site, 'stackexchange.','') -- rest of columns , badgename , userid from #results where badgename is null -- and userid is not null drop table #results
For the enthusiast badge for your account the outcome today is:
The query basically insert for each database if you have an account and if so, if the badge you're looking for exists for your userid. A final query filters out the rows that did return a badgename, leaving the rows with sitenames that you don't have the badge yet.
Keep in mind SEDE is only updated once a week.