Federating TFS tables across collections

UPDATE: the outlined logic will break if your TFS instance has a mix of pre-AzDevOps collections with recently (2019+) created ones. Details here. The linked gist was updated, too.

As I've mentioned earlier, Team Foundation Server stores its data in multiple databases - one database per team project collection, and also a global database called Tfs_Configuration. How is one supposed to run cross-collection queries, then?