This query will use the data available in SEDE to return the complete list of all your badges networkwide.
The SEDE Query
To run it you need your accountid and that one can be found by visiting your network account on StackExchange.com and then take the id from the url. Yours is 3803973, mine is 281857.
-- accountid: Account on stackexchange.com ! "In the url on stackexchange.com"
-- 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
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 #siteurl @rec, @rec output
set @res = @rec
+ '.'
+ substring(@dbname, 0, @pos)
end
end;
go
declare @accountid int = ##accountid:int## -- 93484
-- all databases
declare db_c cursor for select [name]
from sys.databases
where database_id > 5 -- skip master, temp, model, msdb, Data.SE
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_badges ( site nvarchar(250)
, id int
, name nvarchar(40)
, [date] datetime
, [class] int
, tagbased bit
, userid int);
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_badges
select @url
, b.id
, b.name
, b.date
, b.class
, b.tagbased
, b.userid
from badges b
inner join users u on u.id = b.userid
where u.accountid = '+ cast(@accountid as nvarchar) + ';'
exec (@sql)
fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;
/* this is generated
*/
create table #b (dbid int, tagname nvarchar(50), link nvarchar(500));
set nocount on
insert into #b values(80,'Altruist','222/altruist');
insert into #b values(80,'Analytical','1306/analytical');
/* skipped 21710 lines */
insert into #b values(288,'Constable','85/constable');
insert into #b values(288,'Sheriff','86/sheriff');
insert into #b values(288,'Illuminator','92/illuminator');
set nocount off
select replace(site,'.StackExchange','') as [site]
,
case when #b.link is not null then
'http://'
+ site
+ '.com/'
+ 'help/badges/'
+ #b.link
+ '?userid='
+ cast(userid as nvarchar)
+ '|'+
name else
name
end as [badge name or badge tag]
, [date]
, case
when class = 1 then 'Gold'
when class = 2 then 'Slver'
when class = 3 then 'Bronze'
end as Class
, case
when tagbased = 1 then 'tag'
else null
end
from #all_badges
left outer join #b on #b.dbid = #all_badges.dbid and
tagname = name
order by replace(site,'.StackExchange','') asc
, [date] desc
drop table #all_badges
Output
In the output is included columns for the site, the badge link (if available), date the badge is awarded and an indicator if it is a tag badge. The result for me looks like this:
Badge link
The url to a badge on a site looks like this:
http://anime.stackexchange.com/help/badges/9/autobiographer?userid=12801
If you go looking for that 9 in SEDE you won't find it. I opened a feature request to see if it could be added. It was suggested by Shog9 to try the API and that is what I did, without abandoning this query completely. On to version 2 that needs...
JavaScript to get an insert script
The following javascript uses the StackAPI to fetch all NAMED badges for all sites. If you would include the Tag badges as well a server will die. Notice that the script uses a dictionary to translate a sitename to a database id. This is needed to keep the insert script on a reasonable size. My first attempts failed dramatically due to the time the server needed to insert and allocate space for all the data. I had to exclude the Japanese and rusian sites because they broke...my script, the API and/or SEDE, I simply gave up.
var dbs = [
/* generated with SEDE
https://data.stackexchange.com/stackoverflow/query/309683/javascript-array-with-databases-that-an-account-has?accountid=281857&opt.textResults=true
*/
//{ site: 'meta.ru.stackoverflow', dbid: 6},
//{ site: 'ru.stackoverflow', dbid: 7},
{ site: 'meta.woodworking', dbid: 8},
{ site: 'musicfans', dbid: 9},
{ site: 'meta.engineering', dbid: 10},
];
(function () {
"use strict";
var api = 'https://api.stackexchange.com',
version = '2.2',
key = 'bring your own key',
bo = 1,
calls = [],
running = false,
handleAll,
remaining = 300,
error_id;
function safeApiGet(url, cb) {
var call;
if (url !== undefined && cb !== undefined && error_id === undefined) {
calls.push({ url: url, callback: cb });
}
if (!running && remaining > 0 && error_id === undefined && calls.length > 0) {
call = calls.shift();
running = true;
window.setTimeout(function () {
$.get(call.url + '&key=' + key, function (data) {
error_id = data.error_id;
if (error_id !== undefined) {
console.log('%d %s', error_id, data.error_message);
// clear the queue, prepare to stop
calls = [];
}
bo = data.backoff || 0.01;
remaining = data.quota_remaining;
call.callback(data);
}).done(function () {running = false; });
}, bo * 1000);
}
}
function findDatabaseId(site) {
var id = 0,
i;
for (i = 0; i < dbs.length; i = i + 1) {
if (dbs[i].site === site) {
id = dbs[i].dbid;
}
}
if (id === 0) {
console.log('-- %s not in dbs', site);
}
return id;
}
function handleSite(site, dbid) {
safeApiGet(api + '/2.2/badges/name?pagesize=100&order=desc&sort=rank&site=' + site + '&filter=!*JxbBNqy.P75laP_', function (badges) {
var j,
pos,
link,
search = '/badges/';
for (j = 0; j < badges.items.length; j = j + 1) {
link = badges.items[j].link;
pos = link.indexOf(search);
link = link.substr(pos + search.length);
console.log("insert into #b values(%d,'%s','%s');", dbid, badges.items[j].name, link);
}
});
}
function handleSites(sites) {
var i,
site,
dbid;
for (i = 0; i < sites.items.length; i = i + 1) {
site = sites.items[i].api_site_parameter;
console.log('-- %s', site);
dbid = findDatabaseId(site);
if (dbid > 0) {
handleSite(site, dbid);
}
}
}
safeApiGet(api + '/2.2/sites?pagesize=500&filter=!6P-nEDMV8tZyf', handleSites);
handleAll = window.setInterval(function () {
if (calls.length > 0 && remaining > 1) {
safeApiGet();
} else {
window.clearInterval(handleAll);
if (calls.length > 0) {
console.log('%d remaining urls', calls.length);
}
}
}, 2000);
}());
If you run this in your Chrome Dev Console you can save the log that consists of over 21000 insert statements.
From there it is easy. Include the statements in the script. Join the all_badges and b table on database_id and badgename and you'll get just enough info to construct a badge url that brings you on the specific badge for you on that site.
Final
Don't run the javascript without requesting a key. Replace the 'bring your own key' string with the key found when you registered the app. This is to guarantee you have more api-quota.
If the site list changes (sites added) the badges script needs to be rerun. I noticed that some sites don't link their named badge. That is due to a mismatch between database name and api_site_parameter name. I call it an annoyance and leave it for others to fix.
Keep in mind that the data in SEDE is only update once a week, on Monday. So your most recent badges might not be on this list. For those you can fallback to Monica's answer.
