Union of data from all tables with name containing a substring

Question

I have 500+ tables all containing a column called COLUMNDATA. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH. What I am trying to get is a column containing all the unique entries from COLUMNDATA across all of these tables. The problem is made a little more challenging because all tables in the DB contain this column, and I am not interested in data from the tables which aren't called TABLE_NUMBER.

I have tried various recipes including UNION and INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'TABLE%' but to no avail.

If anyone has an idea that would be great! Otherwise I'll just stick with the 1000 line script I've got already. For now I have a nasty but gets-the-job-done

CREATE TABLE TAB_NAME AS SELECT COLUMNDATA FROM TABLE_FIRST UNION SELECT COLUMNDATA FROM TABLE_SECOND UNION . . .

ad infinitum

Answer 1

Yes, you need a script but it doesn't have to be 1000 lines.

--make a table or global temporary table to put the results in

CREATE TABLE TEMP_COLUMN_DATA
( PK_ID NUMBER(9),
  TABLE_SOURCE VARCHAR2(30),
  TEXT_CONTENT VARCHAR2(4000));

--add the primary key of PK_ID, are contents larger than 4000 characters? use a CLOB

--create a trigger on the table if the PK_ID is null add a sequence value

DECLARE
v_text  VARCHAR2(4000);
CURSOR tab_col IS
Select TABLE_NAME from all_tab_cols
where COLUMN_NAME = 'COLUMNDATA'
and table_name LIKE 'TABLE%'
--modify the cursor here to exclude any tables you dont want
--and table_name not in('xxxx','xxxxx','xxxxx')
ORDER BY 1;
BEGIN
  FOR THE_TABS IN TAB_COL LOOP
    V_TEXT := 'INSERT INTO TEMP_COLUMN_DATA SELECT NULL,'
    || THE_TABS.TABLE_NAME 
    ||',' 
    || THE_TABS.TABLE_NAME 
    || '.COLUMNDATA ' 
    || ' from ' 
    || THE_TABS.TABLE_NAME 
    || ' order by 2';
    DBMS_OUTPUT.PUT_LINE(V_TEXT);
    EXECUTE_IMMEDIATE(v_text);
  END LOOP;
 END;

--then do a select distinct columndata from temp_column_data