Creates a federating view of all instances of a TFS table in a separate database, for cross-collection queries

ImportTFSTable.sql

CREATE PROC dbo.ImportTFSTable(@Owner varchar(100), @Table as varchar(100), @WithConfig as int = 0)
AS
    declare @SQL varchar(max), @FieldSet varchar(max)
    set @FieldSet = (select '[' + name + ']' as a from Tfs_DefaultCollection.sys.columns
        where [object_id]=object_id('Tfs_Contracts.'+@Owner+'.'+@Table)
        order by column_id
        for xml path(''))
    set @FieldSet = replace(substring(@FieldSet, 4, len(@FieldSet) - 7), '</a><a>', ',')

    set @SQL = (select 'select {guid'''+cast(HostId as varchar(36))+'''} as CollID, ' + @FieldSet + ' from [' + replace(substring(DatabaseName, charindex(';', DatabaseName) + 1, 200), '"', '') + '].' + @Owner+ '.' + @Table as a
        from Tfs_Configuration.dbo.tbl_ServiceHost c
            inner join Tfs_Configuration.dbo.tbl_Database d on c.DatabaseId = d.DatabaseId
        where ParentHostId is not null for xml path(''))
    set @SQL = replace(substring(@SQL, 4, len(@SQL) - 7), '</a><a>', ' union '+char(13))
    if @WithConfig <> 0
    begin
        set @SQL += ' union select {guid''00000000-0000-0000-0000-000000000000''} as CollID, * from Tfs_Configuration.' + @Owner + '.' + @Table
    end
	
    if object_id(@Table) is not null
        set @SQL = 'alter view dbo.' + @Table + ' as ' + @SQL
    else
        set @SQL = 'create view dbo.' + @Table + ' as ' + @SQL

    execute(@SQL)
GO

This is a companion gist for this blog post: https://rathertech.blogspot.com/2018/12/federating-tfs-tables-across-collections.html

Updated to support instances with a mix of legacy (pre-AzDevOps) collections and recently (2019+) created ones. The database name prefix and field order is different between the two. Also, there's support for federating a table between all collections and Tfs_Configuration; there's at least one table where it's useful, Task.tbl_TaskDefinition.

For Azure DevOps instances that were never upgraded from TFS (i. e. installed from scratch as version 2019 or later), the hard-coded database names Tfs_Configuration and Tfs_DefaultCollection that this gist uses might be off; the internal database name prefix is, apparently, AzureDevOps_ now.