Union of data from all tables with name containing a substring
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