s.t.nguyen s.t.nguyen - 1 month ago 6
SQL Question

How to select a column from all tables in which it resides?

I have many tables that have the same column 'customer_number'.
I can get a list of all these table by query:

SELECT table_name FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = 'customer_number';


The question is how do I get all the records that have a specific customer number from all these tables without running the same query against each of them.

Answer

I assume you want to automate this. Two approaches.

  1. SQL to generate SQL scripts

.

spool run_rep.sql 
set head off pages 0 lines 200 trimspool on feedback off

SELECT 'prompt ' || table_name || chr(10) || 

       'select ''' || table_name ||
         ''' tname, CUSTOMER_NUMBER from ' || table_name || ';' cmd
FROM all_tab_columns
WHERE column_name = 'CUSTOMER_NUMBER';

spool off

@ run_rep.sql
  1. PLSQL

Similar idea to use dynamic sql:

DECLARE
   TYPE rcType IS REF CURSOR;
   rc   rcType;
   CURSOR c1 IS SELECT table_name FROM all_table_columns WHERE column_name = 'CUST_NUM';
   cmd   VARCHAR2(4000);
   cNum  NUMBER;
BEGIN
   FOR r1 IN c1 LOOP
      cmd := 'SELECT cust_num FROM ' || r1.table_name ;
      OPEN rc FOR cmd;
      LOOP
         FETCH rc INTO cNum;
         EXIT WHEN rc%NOTFOUND;
         -- Prob best to INSERT this into a temp table and then
         -- select * that to avoind DBMS_OUTPUT buffer full issues
         DBMS_OUTPUT.PUT_LINE ( 'T:' || r1.table_name || ' C: ' || rc.cust_num );
      END LOOP;
      CLOSE rc;
   END LOOP;
END;