If you created this database structure, please read up on database normalisation and correct the structure before you go any further down this design.

If someone else created it and they are still around, go and puch them on the nose.

If they are not around go and punch the wall.

If you have to stick with this design, I would create a table of agents and a stored procedure that writes a view using dynamic SQL that is triggered every time the agents table is altered.

the view with UNION ALL the same query once for each agent table

The problem is that any other view or stored procedure that uses that generated view will also need to be recompiled every time the view changes.

SERIOUSLY, get the data normalised.