query all tables in Oracle

Question

I have this plsql code that will give me all tables in the database with name CUSTOMERS. Now I am struck how to insert another loop in to this. I want to get the output from this code and pass it on to next loop where I want to query something like, select count(*) from Schema.customers; for each schema.

DECLARE
    --c_id customers.id%type;
    c_name all_tables.table_name%type;
    c_tabs all_tables.owner%type;
    CURSOR c_tables is
      SELECT table_name, owner FROM all_tables where table_name='CUSTOMERS';
        BEGIN
        OPEN c_tables;
        LOOP
        FETCH c_tables into c_name, c_tabs;
        dbms_output.put_line(c_tabs || '.' || c_name );
        EXIT WHEN c_tables%notfound;
        END LOOP;
        CLOSE c_tables;
    END;
    /

------- Sample output of my code: ------------

UMICH2.CUSTOMERS
TRINITYDC.CUSTOMERS
BUFFALO.CUSTOMERS
SNOW.CUSTOMERS
PULASKITECH.CUSTOMERS
RARITANVAL.CUSTOMERS
STMARYSCA.CUSTOMERS

possible duplicate of [Dynamically creating and executing sql commands in oracle](http://stackoverflow.com/questions/2284079/dynamically-creating-and-executing-sql-commands-in-oracle) — Bulat, Aug 29 '14 at 16:04
also http://stackoverflow.com/questions/22216805/executing-dynamic-sql-query-in-oracle — Bulat, Aug 29 '14 at 16:06

score 1 · Answer 1 · answered Aug 30 '14 at 10:52

You can get the same result in a single SQL statement

SELECT table_name
      ,to_number
         (extractvalue
            (xmltype
               (dbms_xmlgen.getxml
                  ('SELECT count(*) c FROM ' || owner || '.' || table_name)
               )
            ,'/ROWSET/ROW/C'
            )
         ) Count
  FROM all_tables
 WHERE table_name = 'CUSTOMERS'

score 0 · Answer 2 · answered Aug 29 '14 at 20:35

This way is one possibility that you can do that.

DECLARE
  --c_id customers.id%type;
  c_name  all_tables.table_name%TYPE;
  c_tabs  all_tables.owner%TYPE;

  v_value PLS_INTEGER;


  CURSOR c_tables  IS
    SELECT table_name
      FROM all_tables
     WHERE table_name = 'CUSTOMERS';

  CURSOR c_owner  IS
    SELECT DISTINCT owner
      FROM all_tables
     WHERE table_name = 'CUSTOMERS';
BEGIN
  OPEN c_tables;

  LOOP
    FETCH c_tables INTO c_name;

    OPEN c_owner;

    LOOP
      FETCH c_owner INTO c_tabs;

      BEGIN
        EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || c_tabs || '.' || c_name INTO v_value;
      EXCEPTION
        WHEN other THEN
          NULL;
      END;

      DBMS_OUTPUT.put_line ( v_value );
      EXIT WHEN c_owner%NOTFOUND;
    END LOOP;

    CLOSE c_owner;

    DBMS_OUTPUT.put_line ( c_tabs || '.' || c_name );
    EXIT WHEN c_tables%NOTFOUND;
  END LOOP;

  CLOSE c_tables;
END;

Any question just let me know.

Thanks.

query all tables in Oracle

2 Answers2