Selecting from random amount of tables with identical structure

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:

• surname

• town

Two tables equalling two address books exist:

• donaldduck

• daisyduck

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.

I am not proud of publicly writing something like this.

Sample + data:

CREATE TABLE donaldduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) );
CREATE TABLE daisyduck  ( SURNAME VARCHAR(100), TOWN VARCHAR(100) );
CREATE TABLE goofy      ( something number(1), SURNAME VARCHAR(100), TOWN VARCHAR(100) );

INSERT INTO DONALDDUCK ( SURNAME, TOWN ) VALUES ( 'Dagobert Duck', 'Entenhausen' );
INSERT INTO daisyduck ( SURNAME, TOWN ) VALUES ( 'Daisy Duck', 'Entenhausen' );
INSERT INTO goofy ( SOMETHING, SURNAME, TOWN ) VALUES (   1, 'Goofy Dog', 'Entenhausen' );
commit;

Types and PL/SQL using a pipelined function:

create type t_NAME_OF_USER as object
(
    SURNAME VARCHAR(100),
    TOWN VARCHAR(100)
);
/

create type t_name_of_user_tab IS TABLE OF t_NAME_OF_USER;
/

CREATE OR REPLACE FUNCTION get_surname_town RETURN t_name_of_user_tab PIPELINED AS
  rc sys_refcursor;
  query clob;
  l_surname varchar2(100);
  l_town varchar2(100);
begin
  for t in (
    select table_name from user_tables ut
    where
      'SURNAME' in (select column_name from user_tab_columns utc where utc.table_name = ut.table_name) and
      'TOWN'  in (select column_name from user_tab_columns utc where utc.table_name = ut.table_name)
    )
  loop
    open rc for 'select surname, town from ' || t.table_name ;
    loop
      fetch rc into l_surname, l_town;
      exit when rc%notfound;
      pipe row(t_NAME_OF_USER(l_surname, l_town));
    end loop;
  end loop;
end;
/

SELECT:

select * from table(get_surname_town);

SURNAME                        TOWN
------------------------------ --------------------
Daisy Duck                     Entenhausen
Dagobert Duck                  Entenhausen
Goofy Dog                      Entenhausen

StackExchange Database Administrators Q#130069, answer score: 12