It's AzDevOps all the way down

Oy vey. Some posts ago, I've mentioned that all TFS Azure DevOps collection databases had the same schema, haven't I? Well, not quite.

The team collections in my TFS setup all go back a few years. They were created when the instance was running TFS 2015 or even TFS 2013. Since then, we had no new collections - until a couple of weeks ago. We've created a new collection, and it threw a major monkey wrench into the database-based reporting works.

For starters, the name of the new collection database is not Tfs_CollectionName; it's AzureDevOps_CollectionName. So the federation query generator was thrown off right away. Fortunately, one can join tbl_Host to Tfs_Configuration.dbo.tbl_Database to resolve that.

In addition, they've rearranged the order of columns in some of the tables I was federating. As far as I could see, they didn't introduce any substantive schema discrepancies between legacy collections and modern ones, but the column order difference made federation by SELECT *... UNION impossible. Now the procedure has to spell out the exact field names for each table by selecting from a system view:

    set @FieldSet = (select '[' + name + ']' as a from Tfs_DefaultCollection.sys.columns

        where [object_id]=object_id('Tfs_DefaultCollection.'+@Owner+'.'+@Table)

        order by column_id

        for xml path(''))

    set @FieldSet = replace(substring(@FieldSet, 4, len(@FieldSet) - 7), '</a><a>', ',')

For on-prem instances of AzDevOps that were never TFS, the database names Tfs_Configuration and Tfs_DefaultCollection that I'm using without further reflection might be off, too.

Note: I know about STRING_AGG in MSSQL 2017+. We have an old server.