It would be possible to build a dynamic query that checks for tablenames, and checks if those tables have the columns corresponding to an agent table, and if so includes them in the union, but it would be quite complex, and from an SQL Server point of view, like using a Ferrari to plough a field.

Much better to use the relational database in the way it is designed to be used.

Create an Agents_Name Table, with an ID column

Add and AgentName_ID column to your Agent table.

This way, all agents share the same Agent Table, but are identified by AgentName_ID

Then instead of a union, you only query one table, possibly joining to the AgentName table to get the name if needed.