How do I perform a UNION query on a dynamic list of tables?
Yes you can. There are several ways to do this.

1> If you are using the database interactively, then just type in the query.
2> If the database is being accessed via a application or script, write a method in the application/script to create the query required and then run it. You would have a database call to get the list of table names, then create a string with the union query then execute that query.
3> In a similar vein to option 2, most stored procedure languages allow you to dynamically create a query as a string and then execute that string - Check out your DB's documentation to find out how to do this. 

A Mysql example https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure. You may like to use a cursor ( http://dev.mysql.com/doc/refman/5.0/en/cursors.html ) to iterate though the list of tables names to Concat ( http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat ) together the query string.