How can I get URLs and site names for a cross-site SEDE query?

Question

When doing cross-site queries on the Stack Exchange Data Explorer, two of the main problems are 1) getting the URLs right and 2) getting the site names right. For URLs, we have constructions like the bottom half of this query or this one. We (Meta regulars) don't bother with site names (c'mon, everybody knows the database of Video Production Stack Exchange is named StackExchange.Audio ...) but the revision list of the recent post 2020: a year in closing speaks volumes. Even for SE staff it's difficult to get it right, see this comment by @Shog9:

There's an internal instance of SEDE that suffers from the same issue as the public instance, @sonic: it has its own sites table with metadata on the databases it has imported, including name, which is never really updated. To get the proper site names for data like this, one must query the actual Sites table used by the system itself, which is slightly less convenient than just choosing the "run on all sites" option and letting SEDE fill it in. You can observe this same effect in some of my old posts when I was in a hurry.

The main disadvantages of the solutions employed to get the URLs (and a similar solution for site names) is that whenever a new 'exception' is needed (e.g. following a site rename), one needs to update all queries. Fortunately, the old URLs (e.g. https://writers.stackexchange.com) continue to be working. Is there a way we can make a community-maintained solution for this?

I'm sure you've already seen it, but I've added some helper views for this. — Aaron Bertrand, Commented Apr 9 at 14:34
@AaronBertrand yes, that is a brilliant solution, I'll figure out what to do with this Q&A. — Glorfindel, Commented Apr 9 at 15:14

11 revs, 2 users 99% · Accepted Answer · 2022-07-14 16:55:08Z

The table below does not provide the solution, but the accepted answer contains instructions how to use it. The table contains all Q&A sites that are available via SEDE. So e.g. Area 51 and CS50 are deliberately not included in the table.

Feel free to edit this answer when the need arises, e.g. when a site is renamed or a new beta is launched. There is a snippet in the other answer to check if the table is up-to-date. The most important thing is to realize the SEDE queries split on the pipe symbol; therefore it should not be used in this introductory text.

URL	Site name	Database name
https://3dprinting.stackexchange.com	3D Printing	StackExchange.3dprinting
https://3dprinting.meta.stackexchange.com	3D Printing Meta	StackExchange.3dprinting.Meta
https://academia.stackexchange.com	Academia	StackExchange.Academia
https://academia.meta.stackexchange.com	Academia Meta	StackExchange.Academia.Meta
https://ham.stackexchange.com	Amateur Radio	StackExchange.Ham
https://ham.meta.stackexchange.com	Amateur Radio Meta	StackExchange.Ham.Meta
https://android.stackexchange.com	Android Enthusiasts	StackExchange.Android
https://android.meta.stackexchange.com	Android Enthusiasts Meta	StackExchange.Android.Meta
https://anime.stackexchange.com	Anime & Manga	StackExchange.Anime
https://anime.meta.stackexchange.com	Anime & Manga Meta	StackExchange.Anime.Meta
https://arduino.stackexchange.com	Arduino	StackExchange.Arduino
https://arduino.meta.stackexchange.com	Arduino Meta	StackExchange.Arduino.Meta
https://gaming.stackexchange.com	Arqade	StackExchange.Gaming
https://gaming.meta.stackexchange.com	Arqade Meta	StackExchange.Gaming.Meta
https://ai.stackexchange.com	Artificial Intelligence	StackExchange.Ai
https://ai.meta.stackexchange.com	Artificial Intelligence Meta	StackExchange.Ai.Meta
https://crafts.stackexchange.com	Arts & Crafts	StackExchange.Crafts
https://crafts.meta.stackexchange.com	Arts & Crafts Meta	StackExchange.Crafts.Meta
https://apple.stackexchange.com	Ask Different	StackExchange.Apple
https://apple.meta.stackexchange.com	Ask Different Meta	StackExchange.Apple.Meta
https://patents.stackexchange.com	Ask Patents	StackExchange.Patents
https://patents.meta.stackexchange.com	Ask Patents Meta	StackExchange.Patents.Meta
https://askubuntu.com	Ask Ubuntu	StackExchange.Ubuntu
https://meta.askubuntu.com	Ask Ubuntu Meta	StackExchange.Ubuntu.Meta
https://astronomy.stackexchange.com	Astronomy	StackExchange.Astronomy
https://astronomy.meta.stackexchange.com	Astronomy Meta	StackExchange.Astronomy.Meta
https://aviation.stackexchange.com	Aviation	StackExchange.Aviation
https://aviation.meta.stackexchange.com	Aviation Meta	StackExchange.Aviation.Meta
https://alcohol.stackexchange.com	Beer, Wine & Spirits	StackExchange.Beer
https://alcohol.meta.stackexchange.com	Beer, Wine & Spirits Meta	StackExchange.Beer.Meta
https://hermeneutics.stackexchange.com	Biblical Hermeneutics	StackExchange.Hermeneutics
https://hermeneutics.meta.stackexchange.com	Biblical Hermeneutics Meta	StackExchange.Hermeneutics.Meta
https://bicycles.stackexchange.com	Bicycles	StackExchange.Bicycles
https://bicycles.meta.stackexchange.com	Bicycles Meta	StackExchange.Bicycles.Meta
https://bioacoustics.stackexchange.com	Bioacoustics	StackExchange.Bioacoustics
https://bioacoustics.meta.stackexchange.com	Bioacoustics Meta	StackExchange.Bioacoustics.Meta
https://bioinformatics.stackexchange.com	Bioinformatics	StackExchange.Bioinformatics
https://bioinformatics.meta.stackexchange.com	Bioinformatics Meta	StackExchange.Bioinformatics.Meta
https://biology.stackexchange.com	Biology	StackExchange.Biology
https://biology.meta.stackexchange.com	Biology Meta	StackExchange.Biology.Meta
https://bitcoin.stackexchange.com	Bitcoin	StackExchange.Bitcoin
https://bitcoin.meta.stackexchange.com	Bitcoin Meta	StackExchange.Bitcoin.Meta
https://blender.stackexchange.com	Blender	StackExchange.Blender
https://blender.meta.stackexchange.com	Blender Meta	StackExchange.Blender.Meta
https://boardgames.stackexchange.com	Board & Card Games	StackExchange.Boardgames
https://boardgames.meta.stackexchange.com	Board & Card Games Meta	StackExchange.Boardgames.Meta
https://bricks.stackexchange.com	Bricks	StackExchange.Bricks
https://bricks.meta.stackexchange.com	Bricks Meta	StackExchange.Bricks.Meta
https://buddhism.stackexchange.com	Buddhism	StackExchange.Buddhism
https://buddhism.meta.stackexchange.com	Buddhism Meta	StackExchange.Buddhism.Meta
https://cardano.stackexchange.com	Cardano	StackExchange.Cardano
https://cardano.meta.stackexchange.com	Cardano Meta	StackExchange.Cardano.Meta
https://chemistry.stackexchange.com	Chemistry	StackExchange.Chemistry
https://chemistry.meta.stackexchange.com	Chemistry Meta	StackExchange.Chemistry.Meta
https://chess.stackexchange.com	Chess	StackExchange.Chess
https://chess.meta.stackexchange.com	Chess Meta	StackExchange.Chess.Meta
https://chinese.stackexchange.com	Chinese Language	StackExchange.Chinese
https://chinese.meta.stackexchange.com	Chinese Language Meta	StackExchange.Chinese.Meta
https://christianity.stackexchange.com	Christianity	StackExchange.Christianity
https://christianity.meta.stackexchange.com	Christianity Meta	StackExchange.Christianity.Meta
https://civicrm.stackexchange.com	CiviCRM	StackExchange.Civicrm
https://civicrm.meta.stackexchange.com	CiviCRM Meta	StackExchange.Civicrm.Meta
https://codegolf.stackexchange.com	Code Golf	StackExchange.Codegolf
https://codegolf.meta.stackexchange.com	Code Golf Meta	StackExchange.Codegolf.Meta
https://codereview.stackexchange.com	Code Review	StackExchange.Codereview
https://codereview.meta.stackexchange.com	Code Review Meta	StackExchange.Codereview.Meta
https://coffee.stackexchange.com	Coffee	StackExchange.Coffee
https://coffee.meta.stackexchange.com	Coffee Meta	StackExchange.Coffee.Meta
https://communitybuilding.stackexchange.com	Community Building	StackExchange.Moderators
https://communitybuilding.meta.stackexchange.com	Community Building Meta	StackExchange.Moderators.Meta
https://scicomp.stackexchange.com	Computational Science	StackExchange.Scicomp
https://scicomp.meta.stackexchange.com	Computational Science Meta	StackExchange.Scicomp.Meta
https://computergraphics.stackexchange.com	Computer Graphics	StackExchange.Computergraphics
https://computergraphics.meta.stackexchange.com	Computer Graphics Meta	StackExchange.Computergraphics.Meta
https://cs.stackexchange.com	Computer Science	StackExchange.Cs
https://cseducators.stackexchange.com	Computer Science Educators	StackExchange.Cseducators
https://cseducators.meta.stackexchange.com	Computer Science Educators Meta	StackExchange.Cseducators.Meta
https://cs.meta.stackexchange.com	Computer Science Meta	StackExchange.Cs.Meta
https://conlang.stackexchange.com	Constructed Languages	StackExchange.Conlang
https://conlang.meta.stackexchange.com	Constructed Languages Meta	StackExchange.Conlang.Meta
https://craftcms.stackexchange.com	Craft CMS	StackExchange.Craftcms
https://craftcms.meta.stackexchange.com	Craft CMS Meta	StackExchange.Craftcms.Meta
https://stats.stackexchange.com	Cross Validated	StackExchange.Stats
https://stats.meta.stackexchange.com	Cross Validated Meta	StackExchange.Stats.Meta
https://crypto.stackexchange.com	Cryptography	StackExchange.Crypto
https://crypto.meta.stackexchange.com	Cryptography Meta	StackExchange.Crypto.Meta
https://datascience.stackexchange.com	Data Science	StackExchange.Datascience
https://datascience.meta.stackexchange.com	Data Science Meta	StackExchange.Datascience.Meta
https://dba.stackexchange.com	Database Administrators	StackExchange.Dba
https://dba.meta.stackexchange.com	Database Administrators Meta	StackExchange.Dba.Meta
https://devops.stackexchange.com	DevOps	StackExchange.Devops
https://devops.meta.stackexchange.com	DevOps Meta	StackExchange.Devops.Meta
https://drones.stackexchange.com	Drones and Model Aircraft	StackExchange.Drones
https://drones.meta.stackexchange.com	Drones and Model Aircraft Meta	StackExchange.Drones.Meta
https://drupal.stackexchange.com	Drupal Answers	StackExchange.Drupal
https://drupal.meta.stackexchange.com	Drupal Answers Meta	StackExchange.Drupal.Meta
https://earthscience.stackexchange.com	Earth Science	StackExchange.Earthscience
https://earthscience.meta.stackexchange.com	Earth Science Meta	StackExchange.Earthscience.Meta
https://ebooks.stackexchange.com	Ebooks	StackExchange.Ebooks
https://ebooks.meta.stackexchange.com	Ebooks Meta	StackExchange.Ebooks.Meta
https://economics.stackexchange.com	Economics	StackExchange.Economics
https://economics.meta.stackexchange.com	Economics Meta	StackExchange.Economics.Meta
https://electronics.stackexchange.com	Electrical Engineering	StackExchange.Electronics
https://electronics.meta.stackexchange.com	Electrical Engineering Meta	StackExchange.Electronics.Meta
https://elementaryos.stackexchange.com	elementary OS	StackExchange.Elementaryos
https://elementaryos.meta.stackexchange.com	elementary OS Meta	StackExchange.Elementaryos.Meta
https://emacs.stackexchange.com	Emacs	StackExchange.Emacs
https://emacs.meta.stackexchange.com	Emacs Meta	StackExchange.Emacs.Meta
https://engineering.stackexchange.com	Engineering	StackExchange.Engineering
https://engineering.meta.stackexchange.com	Engineering Meta	StackExchange.Engineering.Meta
https://english.stackexchange.com	English Language & Usage	StackExchange.English
https://english.meta.stackexchange.com	English Language & Usage Meta	StackExchange.English.Meta
https://ell.stackexchange.com	English Language Learners	StackExchange.Ell
https://ell.meta.stackexchange.com	English Language Learners Meta	StackExchange.Ell.Meta
https://eosio.stackexchange.com	EOS.IO	StackExchange.Eosio
https://eosio.meta.stackexchange.com	EOS.IO Meta	StackExchange.Eosio.Meta
https://esperanto.stackexchange.com	Esperanto Language	StackExchange.Esperanto
https://esperanto.meta.stackexchange.com	Esperanto Language Meta	StackExchange.Esperanto.Meta
https://ethereum.stackexchange.com	Ethereum	StackExchange.Ethereum
https://ethereum.meta.stackexchange.com	Ethereum Meta	StackExchange.Ethereum.Meta
https://expatriates.stackexchange.com	Expatriates	StackExchange.Expatriates
https://expatriates.meta.stackexchange.com	Expatriates Meta	StackExchange.Expatriates.Meta
https://expressionengine.stackexchange.com	ExpressionEngine® Answers	StackExchange.Expressionengine
https://expressionengine.meta.stackexchange.com	ExpressionEngine® Answers Meta	StackExchange.Expressionengine.Meta
https://freelancing.stackexchange.com	Freelancing	StackExchange.Freelancing
https://freelancing.meta.stackexchange.com	Freelancing Meta	StackExchange.Freelancing.Meta
https://french.stackexchange.com	French Language	StackExchange.French
https://french.meta.stackexchange.com	French Language Meta	StackExchange.French.Meta
https://gamedev.stackexchange.com	Game Development	StackExchange.GameDev
https://gamedev.meta.stackexchange.com	Game Development Meta	StackExchange.GameDev.Meta
https://gardening.stackexchange.com	Gardening & Landscaping	StackExchange.Gardening
https://gardening.meta.stackexchange.com	Gardening & Landscaping Meta	StackExchange.Gardening.Meta
https://genealogy.stackexchange.com	Genealogy & Family History	StackExchange.Genealogy
https://genealogy.meta.stackexchange.com	Genealogy & Family History Meta	StackExchange.Genealogy.Meta
https://gis.stackexchange.com	Geographic Information Systems	StackExchange.Gis
https://gis.meta.stackexchange.com	Geographic Information Systems Meta	StackExchange.Gis.Meta
https://german.stackexchange.com	German Language	StackExchange.German
https://german.meta.stackexchange.com	German Language Meta	StackExchange.German.Meta
https://graphicdesign.stackexchange.com	Graphic Design	StackExchange.Graphicdesign
https://graphicdesign.meta.stackexchange.com	Graphic Design Meta	StackExchange.Graphicdesign.Meta
https://hardwarerecs.stackexchange.com	Hardware Recommendations	StackExchange.Hardwarerecs
https://hardwarerecs.meta.stackexchange.com	Hardware Recommendations Meta	StackExchange.Hardwarerecs.Meta
https://hinduism.stackexchange.com	Hinduism	StackExchange.Hinduism
https://hinduism.meta.stackexchange.com	Hinduism Meta	StackExchange.Hinduism.Meta
https://history.stackexchange.com	History	StackExchange.History
https://history.meta.stackexchange.com	History Meta	StackExchange.History.Meta
https://hsm.stackexchange.com	History of Science and Mathematics	StackExchange.Hsm
https://hsm.meta.stackexchange.com	History of Science and Mathematics Meta	StackExchange.Hsm.Meta
https://diy.stackexchange.com	Home Improvement	StackExchange.Diy
https://diy.meta.stackexchange.com	Home Improvement Meta	StackExchange.Diy.Meta
https://homebrew.stackexchange.com	Homebrewing	StackExchange.Homebrew
https://homebrew.meta.stackexchange.com	Homebrewing Meta	StackExchange.Homebrew.Meta
https://security.stackexchange.com	Information Security	StackExchange.Security
https://security.meta.stackexchange.com	Information Security Meta	StackExchange.Security.Meta
https://iot.stackexchange.com	Internet of Things	StackExchange.Iot
https://iot.meta.stackexchange.com	Internet of Things Meta	StackExchange.Iot.Meta
https://interpersonal.stackexchange.com	Interpersonal Skills	StackExchange.Interpersonal
https://interpersonal.meta.stackexchange.com	Interpersonal Skills Meta	StackExchange.Interpersonal.Meta
https://iota.stackexchange.com	Iota	StackExchange.Iota
https://iota.meta.stackexchange.com	Iota Meta	StackExchange.Iota.Meta
https://islam.stackexchange.com	Islam	StackExchange.Islam
https://islam.meta.stackexchange.com	Islam Meta	StackExchange.Islam.Meta
https://italian.stackexchange.com	Italian Language	StackExchange.Italian
https://italian.meta.stackexchange.com	Italian Language Meta	StackExchange.Italian.Meta
https://japanese.stackexchange.com	Japanese Language	StackExchange.Japanese
https://japanese.meta.stackexchange.com	Japanese Language Meta	StackExchange.Japanese.Meta
https://joomla.stackexchange.com	Joomla	StackExchange.Joomla
https://joomla.meta.stackexchange.com	Joomla Meta	StackExchange.Joomla.Meta
https://korean.stackexchange.com	Korean Language	StackExchange.Korean
https://korean.meta.stackexchange.com	Korean Language Meta	StackExchange.Korean.Meta
https://languagelearning.stackexchange.com	Language Learning	StackExchange.Languagelearning
https://languagelearning.meta.stackexchange.com	Language Learning Meta	StackExchange.Languagelearning.Meta
https://latin.stackexchange.com	Latin Language	StackExchange.Latin
https://latin.meta.stackexchange.com	Latin Language Meta	StackExchange.Latin.Meta
https://law.stackexchange.com	Law	StackExchange.Law
https://law.meta.stackexchange.com	Law Meta	StackExchange.Law.Meta
https://lifehacks.stackexchange.com	Lifehacks	StackExchange.Lifehacks
https://lifehacks.meta.stackexchange.com	Lifehacks Meta	StackExchange.Lifehacks.Meta
https://linguistics.stackexchange.com	Linguistics	StackExchange.Linguistics
https://linguistics.meta.stackexchange.com	Linguistics Meta	StackExchange.Linguistics.Meta
https://literature.stackexchange.com	Literature	StackExchange.Literature
https://literature.meta.stackexchange.com	Literature Meta	StackExchange.Literature.Meta
https://magento.stackexchange.com	Magento	StackExchange.Magento
https://magento.meta.stackexchange.com	Magento Meta	StackExchange.Magento.Meta
https://martialarts.stackexchange.com	Martial Arts	StackExchange.Martialarts
https://martialarts.meta.stackexchange.com	Martial Arts Meta	StackExchange.Martialarts.Meta
https://mathematica.stackexchange.com	Mathematica	StackExchange.Mathematica
https://mathematica.meta.stackexchange.com	Mathematica Meta	StackExchange.Mathematica.Meta
https://math.stackexchange.com	Mathematics	StackExchange.Math
https://matheducators.stackexchange.com	Mathematics Educators	StackExchange.Matheducators
https://matheducators.meta.stackexchange.com	Mathematics Educators Meta	StackExchange.Matheducators.Meta
https://math.meta.stackexchange.com	Mathematics Meta	StackExchange.Math.Meta
https://mathoverflow.net	MathOverflow	StackExchange.Mathoverflow
https://meta.mathoverflow.net	MathOverflow Meta	StackExchange.Mathoverflow.Meta
https://mattermodeling.stackexchange.com	Matter Modeling	StackExchange.Materials
https://mattermodeling.meta.stackexchange.com	Matter Modeling Meta	StackExchange.Materials.Meta
https://medicalsciences.stackexchange.com	Medical Sciences	StackExchange.Health
https://medicalsciences.meta.stackexchange.com	Medical Sciences Meta	StackExchange.Health.Meta
https://meta.serverfault.com	Meta Server Fault	ServerFault.Meta
https://meta.stackexchange.com	Meta Stack Exchange	StackExchange.Meta
https://meta.stackoverflow.com	Meta Stack Overflow	StackOverflow.Meta
https://meta.superuser.com	Meta Super User	SuperUser.Meta
https://judaism.stackexchange.com	Mi Yodeya	StackExchange.Judaism
https://judaism.meta.stackexchange.com	Mi Yodeya Meta	StackExchange.Judaism.Meta
https://monero.stackexchange.com	Monero	StackExchange.Monero
https://monero.meta.stackexchange.com	Monero Meta	StackExchange.Monero.Meta
https://mechanics.stackexchange.com	Motor Vehicle Maintenance & Repair	StackExchange.Garage
https://mechanics.meta.stackexchange.com	Motor Vehicle Maintenance & Repair Meta	StackExchange.Garage.Meta
https://movies.stackexchange.com	Movies & TV	StackExchange.Movies
https://movies.meta.stackexchange.com	Movies & TV Meta	StackExchange.Movies.Meta
https://musicfans.stackexchange.com	Music Fans	StackExchange.Musicfans
https://musicfans.meta.stackexchange.com	Music Fans Meta	StackExchange.Musicfans.Meta
https://music.stackexchange.com	Music: Practice & Theory	StackExchange.Music
https://music.meta.stackexchange.com	Music: Practice & Theory Meta	StackExchange.Music.Meta
https://mythology.stackexchange.com	Mythology & Folklore	StackExchange.Mythology
https://mythology.meta.stackexchange.com	Mythology & Folklore Meta	StackExchange.Mythology.Meta
https://networkengineering.stackexchange.com	Network Engineering	StackExchange.Networkengineering
https://networkengineering.meta.stackexchange.com	Network Engineering Meta	StackExchange.Networkengineering.Meta
https://opendata.stackexchange.com	Open Data	StackExchange.Opendata
https://opendata.meta.stackexchange.com	Open Data Meta	StackExchange.Opendata.Meta
https://opensource.stackexchange.com	Open Source	StackExchange.Opensource
https://opensource.meta.stackexchange.com	Open Source Meta	StackExchange.Opensource.Meta
https://or.stackexchange.com	Operations Research	StackExchange.Or
https://or.meta.stackexchange.com	Operations Research Meta	StackExchange.Or.Meta
https://parenting.stackexchange.com	Parenting	StackExchange.Parenting
https://parenting.meta.stackexchange.com	Parenting Meta	StackExchange.Parenting.Meta
https://money.stackexchange.com	Personal Finance & Money	StackExchange.Money
https://money.meta.stackexchange.com	Personal Finance & Money Meta	StackExchange.Money.Meta
https://pets.stackexchange.com	Pets	StackExchange.Pets
https://pets.meta.stackexchange.com	Pets Meta	StackExchange.Pets.Meta
https://philosophy.stackexchange.com	Philosophy	StackExchange.Philosophy
https://philosophy.meta.stackexchange.com	Philosophy Meta	StackExchange.Philosophy.Meta
https://photo.stackexchange.com	Photography	StackExchange.Photography
https://photo.meta.stackexchange.com	Photography Meta	StackExchange.Photography.Meta
https://fitness.stackexchange.com	Physical Fitness	StackExchange.Fitness
https://fitness.meta.stackexchange.com	Physical Fitness Meta	StackExchange.Fitness.Meta
https://physics.stackexchange.com	Physics	StackExchange.Physics
https://physics.meta.stackexchange.com	Physics Meta	StackExchange.Physics.Meta
https://poker.stackexchange.com	Poker	StackExchange.Poker
https://poker.meta.stackexchange.com	Poker Meta	StackExchange.Poker.Meta
https://politics.stackexchange.com	Politics	StackExchange.Politics
https://politics.meta.stackexchange.com	Politics Meta	StackExchange.Politics.Meta
https://portuguese.stackexchange.com	Portuguese Language	StackExchange.Portuguese
https://portuguese.meta.stackexchange.com	Portuguese Language Meta	StackExchange.Portuguese.Meta
https://pm.stackexchange.com	Project Management	StackExchange.Pm
https://pm.meta.stackexchange.com	Project Management Meta	StackExchange.Pm.Meta
https://proofassistants.stackexchange.com	Proof Assistants	StackExchange.Proofassistants
https://proofassistants.meta.stackexchange.com	Proof Assistants Meta	StackExchange.Proofassistants.Meta
https://psychology.stackexchange.com	Psychology & Neuroscience	StackExchange.Cogsci
https://psychology.meta.stackexchange.com	Psychology & Neuroscience Meta	StackExchange.Cogsci.Meta
https://puzzling.stackexchange.com	Puzzling	StackExchange.Puzzling
https://puzzling.meta.stackexchange.com	Puzzling Meta	StackExchange.Puzzling.Meta
https://quant.stackexchange.com	Quantitative Finance	StackExchange.Quant
https://quant.meta.stackexchange.com	Quantitative Finance Meta	StackExchange.Quant.Meta
https://quantumcomputing.stackexchange.com	Quantum Computing	StackExchange.Quantumcomputing
https://quantumcomputing.meta.stackexchange.com	Quantum Computing Meta	StackExchange.Quantumcomputing.Meta
https://raspberrypi.stackexchange.com	Raspberry Pi	StackExchange.Raspberrypi
https://raspberrypi.meta.stackexchange.com	Raspberry Pi Meta	StackExchange.Raspberrypi.Meta
https://retrocomputing.stackexchange.com	Retrocomputing	StackExchange.Retrocomputing
https://retrocomputing.meta.stackexchange.com	Retrocomputing Meta	StackExchange.Retrocomputing.Meta
https://reverseengineering.stackexchange.com	Reverse Engineering	StackExchange.Reverseengineering
https://reverseengineering.meta.stackexchange.com	Reverse Engineering Meta	StackExchange.Reverseengineering.Meta
https://robotics.stackexchange.com	Robotics	StackExchange.Robotics
https://robotics.meta.stackexchange.com	Robotics Meta	StackExchange.Robotics.Meta
https://rpg.stackexchange.com	Role-playing Games	StackExchange.Rpg
https://rpg.meta.stackexchange.com	Role-playing Games Meta	StackExchange.Rpg.Meta
https://russian.stackexchange.com	Russian Language	StackExchange.Russian
https://russian.meta.stackexchange.com	Russian Language Meta	StackExchange.Russian.Meta
https://salesforce.stackexchange.com	Salesforce	StackExchange.Salesforce
https://salesforce.meta.stackexchange.com	Salesforce Meta	StackExchange.Salesforce.Meta
https://scifi.stackexchange.com	Science Fiction & Fantasy	StackExchange.Scifi
https://scifi.meta.stackexchange.com	Science Fiction & Fantasy Meta	StackExchange.Scifi.Meta
https://cooking.stackexchange.com	Seasoned Advice	StackExchange.Cooking
https://cooking.meta.stackexchange.com	Seasoned Advice Meta	StackExchange.Cooking.Meta
https://serverfault.com	Server Fault	ServerFault
https://sharepoint.stackexchange.com	SharePoint	StackExchange.Sharepoint
https://sharepoint.meta.stackexchange.com	SharePoint Meta	StackExchange.Sharepoint.Meta
https://dsp.stackexchange.com	Signal Processing	StackExchange.Dsp
https://dsp.meta.stackexchange.com	Signal Processing Meta	StackExchange.Dsp.Meta
https://sitecore.stackexchange.com	Sitecore	StackExchange.Sitecore
https://sitecore.meta.stackexchange.com	Sitecore Meta	StackExchange.Sitecore.Meta
https://skeptics.stackexchange.com	Skeptics	StackExchange.Skeptics
https://skeptics.meta.stackexchange.com	Skeptics Meta	StackExchange.Skeptics.Meta
https://solana.stackexchange.com	Solana	StackExchange.Solana
https://solana.meta.stackexchange.com	Solana Meta	StackExchange.Solana.Meta
https://softwareengineering.stackexchange.com	Software Engineering	StackExchange.Programmers
https://softwareengineering.meta.stackexchange.com	Software Engineering Meta	StackExchange.Programmers.Meta
https://sqa.stackexchange.com	Software Quality Assurance & Testing	StackExchange.Sqa
https://sqa.meta.stackexchange.com	Software Quality Assurance & Testing Meta	StackExchange.Sqa.Meta
https://softwarerecs.stackexchange.com	Software Recommendations	StackExchange.Softwarerecs
https://softwarerecs.meta.stackexchange.com	Software Recommendations Meta	StackExchange.Softwarerecs.Meta
https://sound.stackexchange.com	Sound Design	StackExchange.Sound
https://sound.meta.stackexchange.com	Sound Design Meta	StackExchange.Sound.Meta
https://space.stackexchange.com	Space Exploration	StackExchange.Space
https://space.meta.stackexchange.com	Space Exploration Meta	StackExchange.Space.Meta
https://spanish.stackexchange.com	Spanish Language	StackExchange.Spanish
https://spanish.meta.stackexchange.com	Spanish Language Meta	StackExchange.Spanish.Meta
https://sports.stackexchange.com	Sports	StackExchange.Sports
https://sports.meta.stackexchange.com	Sports Meta	StackExchange.Sports.Meta
https://stackapps.com	Stack Apps	StackApps
https://stackoverflow.com	Stack Overflow	StackOverflow
https://pt.stackoverflow.com	Stack Overflow em Português	StackOverflow.Br
https://pt.meta.stackoverflow.com	Stack Overflow em Português Meta	StackOverflow.Br.Meta
https://es.stackoverflow.com	Stack Overflow en español	StackOverflow.Es
https://es.meta.stackoverflow.com	Stack Overflow Meta en español	StackOverflow.Es.Meta
https://ru.stackoverflow.com	Stack Overflow на русском	StackOverflow.Ru
https://ru.meta.stackoverflow.com	Stack Overflow на русском Meta	StackOverflow.Ru.Meta
https://stellar.stackexchange.com	Stellar	StackExchange.Stellar
https://stellar.meta.stackexchange.com	Stellar Meta	StackExchange.Stellar.Meta
https://substrate.stackexchange.com	Substrate and Polkadot	StackExchange.Substrate
https://substrate.meta.stackexchange.com	Substrate and Polkadot Meta	StackExchange.Substrate.Meta
https://superuser.com	Super User	SuperUser
https://sustainability.stackexchange.com	Sustainable Living	StackExchange.Sustainability
https://sustainability.meta.stackexchange.com	Sustainable Living Meta	StackExchange.Sustainability.Meta
https://tex.stackexchange.com	TeX - LaTeX	StackExchange.Tex
https://tex.meta.stackexchange.com	TeX - LaTeX Meta	StackExchange.Tex.Meta
https://tezos.stackexchange.com	Tezos	StackExchange.Tezos
https://tezos.meta.stackexchange.com	Tezos Meta	StackExchange.Tezos.Meta
https://outdoors.stackexchange.com	The Great Outdoors	StackExchange.Outdoors
https://outdoors.meta.stackexchange.com	The Great Outdoors Meta	StackExchange.Outdoors.Meta
https://workplace.stackexchange.com	The Workplace	StackExchange.Workplace
https://workplace.meta.stackexchange.com	The Workplace Meta	StackExchange.Workplace.Meta
https://cstheory.stackexchange.com	Theoretical Computer Science	StackExchange.Cstheory
https://cstheory.meta.stackexchange.com	Theoretical Computer Science Meta	StackExchange.Cstheory.Meta
https://tor.stackexchange.com	Tor	StackExchange.Tor
https://tor.meta.stackexchange.com	Tor Meta	StackExchange.Tor.Meta
https://travel.stackexchange.com	Travel	StackExchange.Travel
https://travel.meta.stackexchange.com	Travel Meta	StackExchange.Travel.Meta
https://tridion.stackexchange.com	Tridion	StackExchange.Tridion
https://tridion.meta.stackexchange.com	Tridion Meta	StackExchange.Tridion.Meta
https://ukrainian.stackexchange.com	Ukrainian Language	StackExchange.Ukrainian
https://ukrainian.meta.stackexchange.com	Ukrainian Language Meta	StackExchange.Ukrainian.Meta
https://unix.stackexchange.com	Unix & Linux	StackExchange.Unix
https://unix.meta.stackexchange.com	Unix & Linux Meta	StackExchange.Unix.Meta
https://ux.stackexchange.com	User Experience	StackExchange.Ux
https://ux.meta.stackexchange.com	User Experience Meta	StackExchange.Ux.Meta
https://vegetarianism.stackexchange.com	Veganism & Vegetarianism	StackExchange.Vegetarian
https://vegetarianism.meta.stackexchange.com	Veganism & Vegetarianism Meta	StackExchange.Vegetarian.Meta
https://vi.stackexchange.com	Vi and Vim	StackExchange.Vi
https://vi.meta.stackexchange.com	Vi and Vim Meta	StackExchange.Vi.Meta
https://video.stackexchange.com	Video Production	StackExchange.Audio
https://video.meta.stackexchange.com	Video Production Meta	StackExchange.Audio.Meta
https://webapps.stackexchange.com	Web Applications	StackExchange.WebApps
https://webapps.meta.stackexchange.com	Web Applications Meta	StackExchange.WebApps.Meta
https://webmasters.stackexchange.com	Webmasters	StackExchange.Webmasters
https://webmasters.meta.stackexchange.com	Webmasters Meta	StackExchange.Webmasters.Meta
https://windowsphone.stackexchange.com	Windows Phone	StackExchange.Windowsphone
https://windowsphone.meta.stackexchange.com	Windows Phone Meta	StackExchange.Windowsphone.Meta
https://woodworking.stackexchange.com	Woodworking	StackExchange.Woodworking
https://woodworking.meta.stackexchange.com	Woodworking Meta	StackExchange.Woodworking.Meta
https://wordpress.stackexchange.com	WordPress Development	StackExchange.Wordpress
https://wordpress.meta.stackexchange.com	WordPress Development Meta	StackExchange.Wordpress.Meta
https://worldbuilding.stackexchange.com	Worldbuilding	StackExchange.Worldbuilding
https://worldbuilding.meta.stackexchange.com	Worldbuilding Meta	StackExchange.Worldbuilding.Meta
https://writing.stackexchange.com	Writing	StackExchange.Writers
https://writing.meta.stackexchange.com	Writing Meta	StackExchange.Writers.Meta
https://rus.stackexchange.com	Русский язык	StackExchange.Rus
https://rus.meta.stackexchange.com	Русский язык Meta	StackExchange.Rus.Meta
https://ja.stackoverflow.com	スタック・オーバーフロー	StackOverflow.Ja
https://ja.meta.stackoverflow.com	スタック・オーバーフローMeta	StackOverflow.Ja.Meta

That list has been generated semi-automatically with some jQuery on stackexchange.com/sites and some Excel formulas, coupled with manual adjustments. It's sorted by site name, and for those sites, the official name of their Meta starts with 'Meta'. — Glorfindel, Commented Jan 20, 2021 at 10:13
OK, that explains those, but 2nd column sorting disrupts mathoverflow.net and the alphabetical adjacency of sites: apple.stackexchange.com ham.stackexchange.com money.stackexchange.com patents.stackexchange.com scicomp.stackexchange.com stats.stackexchange.com crypto.stackexchange.com money.stackexchange.com fitness.stackexchange.com cooking.stackexchange.com dsp.stackexchange.com es.stackoverflow.com outdoors.stackexchange.com workplace.stackexchange.com - — Rob, Commented Jan 20, 2021 at 10:53
Sorting on the first column (like this) works too, but also suffers from e.g. Ask Ubuntu being separated from its own Meta. — Glorfindel, Commented Jan 20, 2021 at 11:14
From the data-explorer tag eventually a SEDE query would be posted as an answer, this additional answer should be the output. --- The written name of the sites is most inconsistent, and really out of scope for this Q&A, but when remembering a site name do we remember "Workplace", "The Workplace" or the URL (asking rhetorically?); while for Raspberry Pi the "The" is missing. --- Perhaps filtering out "meta" from wherever it is in the string of the URL and sorting by the resulting alphanumeric would form the most natural grouping; in the manner by which each site is remembered. ??? — Rob, Commented Jan 20, 2021 at 11:28
@P.Mort.-forgotClayShirky_q I already updated a dozen queries, so I hope so :) — Glorfindel, Commented Jan 25, 2021 at 9:56
Not that they can be used in a SEDE context, but perhaps add a note about the excluded (special) sites: Area 51 and Über Stack Exchange? Are there other sites? Stack Apps is included, but it is special in some ways - without a meta site and "Top Apps"*/"Top Scripts"* sections — This_is_NOT_a_forum, Commented Jan 25, 2021 at 10:12
@P.Mort.-forgotClayShirky_q winterbash2020.stackexchange.com? I'll add a note about it. CS50 is not in SEDE either. — Glorfindel, Commented Jan 25, 2021 at 10:13
In terms of regularity of the URL (and DNS), mathoverflow.net is an exception (like the "trilogy" sites. And Ask Ubuntu). — This_is_NOT_a_forum, Commented Apr 11, 2021 at 5:50
@Glorfindel is this still accurate? I think code golf has a new name in the meanwhile? — Luuklag, Commented Aug 18, 2021 at 19:47
@Luuklag actually, it's still called Code Golf according to e.g. the website title. — Glorfindel, Commented Aug 19, 2021 at 16:56
"code golf & code challenges" (obfuscated as "CGCC") is in the logo for Code Golf. But it is just that, a logo. — This_is_NOT_a_forum, Commented Jun 27, 2023 at 10:55
Coming up (same status as CS50?): Programming Language Design and Implementation — This_is_NOT_a_forum, Commented Jun 27, 2023 at 10:58

Glorfindel · Accepted Answer · 2021-08-19 20:06:24Z

Yes, we can. This question will receive another answer with a table containing all site names, base URLs and database names. That answer can be used in SEDE queries by reading the Markdown from its latest revision and can then be parsed to generate a temporary table with all the relevant information. Here is how that works.

You can use that snippet and JOIN your cross-site query with this table. An example can be found in this query, which lists the number of Hot Network Questions per site:

To check whether the site names in the table in the other answer are up to date (and all sites are included), you can run the following snippet. It will print 'Everything is OK!' if the table is correct.

const key = "U4DMV*8nvpm3EOpvf69Rxw(("; // from API documentation
// Read site list in Meta post
$.get("https://api.stackexchange.com/2.3/posts/359796?site=meta&filter=!).EiO3YPYw&key=" + key, function (data) {
  let markdown = data.items[0].body_markdown;
  let components = markdown.split("|");
  let sites = {};
  for (var i = 9; i < components.length; i += 4) {
    sites[components[i].trim()] = components[i + 1].trim();
  }
  
  // Get site information
  $.get("https://api.stackexchange.com/2.3/sites?pageSize=1000&filter=!*L2qLVG5uyZ8GDTI&key=" + key, function (data) {
    var ok = true;
    for (var i = 0; i < data.items.length; i++) {
      let url = data.items[i].site_url;
      // Some sites are deliberately excluded
      if (url == "https://area51.meta.stackexchange.com" ||
          url == "https://cs50.stackexchange.com" ||
          url == "https://cs50.meta.stackexchange.com")
        continue;
      let name = data.items[i].name;
      if (url in sites) {
        if (sites[url] != name) {
          ok = false;
          console.log("Site " + sites[url] + " has another name: " + name);
        }
        delete sites[url];
      } else {
        ok = false;
        console.log("Site " + name + " (" + url + ") is missing.");
      }
    }
    for (const [key, value] of Object.entries(sites)) {
      ok = false;
      console.log("Site " + value + " (" + key + ") does not exist anymore.");
    }
    if (ok) {
      console.log("Everything is OK!");
    }
  });
});

<script src="https://code.jquery.com/jquery-1.11.1.min.js" type="text/javascript" ></script>

While I don't think it will cause issues in our use case it is worth mentioning that A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table. The query does require that the identity values are consecutive. — rene, Commented Jan 20, 2021 at 10:20
... and it relies on STRING_SPLIT returning its values in the right order, which isn't guaranteed. Let's see what happens with the current version. I like keeping things (reasonably) simple. — Glorfindel, Commented Jan 20, 2021 at 10:35
I've been fiddling a bit to see if I could get that table without using temp tables: data.stackexchange.com/stackoverflow/query/1362177/… — rene, Commented Jan 24, 2021 at 19:53
Nice! Perhaps it's worth making a Code Golf challenge out of it :) — Glorfindel, Commented Jan 25, 2021 at 7:25

Stack Exchange Network

How can I get URLs and site names for a cross-site SEDE query?

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
support
hyperlinks
data-explorer
site-name
.

Linked

Hot Network Questions

How can I get URLs and site names for a cross-site SEDE query?

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged supporthyperlinksdata-explorersite-name.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
support
hyperlinks
data-explorer
site-name
.