I am programming along the lines of an unfavorable design. I have no influence on the schema because it belongs to a readymade standard software.
For simplicity let's say this software stores address books of several users. Each table is assigned with the name of the corresponding user.
The tables are created like this:
CREATE TABLE NAME_OF_USER ( SURNAME VARCHAR(100), TOWN VARCHAR(100) )
So all tables have these columns:
Two tables equalling two address books exist:
The address books have been filled like this:
INSERT INTO DONALDDUCK ( SURNAME, TOWN ) VALUES ( 'Dagobert Duck', 'Entenhausen' )
In order to retrieve a comprehensive list of addresses I could run
Select * from donaldduck Select * from daisyduck
But what if someone adds an address book for GOOFY ? I would have to change my query for every newly created address book, in this case adding a
SELECT * FROM GOOFY as the third row.
To make this even more complicated there are other tables with a different setup (they are storing other information which is totally irrelevant for my task of listing all entries from the given address books). So I can not simply iterate over all existing tables because this would lead to processing tables which I do not need in my query.
Is there a way to say "select * from all tables which contain a column named town" ? This should be solved by using plain SQL or PL/SQL because I want to avoid having to code this in a high level language like I think I would need to be doing by following advice from jsapkota.