Find tables with specific criteria - Oracle 12c

Question

How to find tables in a schema that start with NUM_ whose data is more than 0 records.

Eg: Consider I'm a couple of schemas

1. Schema1
2. Schema2
3. Schema3

Schema1 has tables:

• NUM_table11 (has 0 records)
• NUM_table12 (has 20 records)
• DummyTable1 (has 10 records)

Schema2 has tables:

• NUM_table21 (has 0 records)
• NUM_table22 (has 20 records)
• DummyTable2 (has 10 records)

Schema3 has tables:

• NUM_table31 (has 0 records)
• NUM_table32 (has 20 records)
• DummyTable3 (has 10 records)

I want to get only Schema2 tables, whose name starts with NUM_ and has more than 0 records. i.e, in this case NUM_table22

Can anyone help on achieving this?

Answer 1

I don't have your tables (and don't feel like creating ones either), so I'll show it on another example - looking for the EMP tables throughout my database.

Connect as a privileged user, the one that has access to DBA_TABLES (as you want to search all users, right?). One of those is SYS, if you don't have any other.

Then write a PL/SQL block which looks into the DBA_TABLES, searches for tables whose name begins with EMP. Dynamic SQL (i.e. execute immediate) counts rows in those tables and - if it is a positive number - returns that table as an output.

SQL> show user
USER is "SYS"
SQL> set serveroutput on
SQL>
SQL> declare
  2    l_cnt number;
  3  begin
  4    for cur_r in (select owner, table_name
  5                  from dba_tables
  6                  where table_name like 'EMP%'
  7                 )
  8    loop
  9      execute immediate 'select count(*) from ' || cur_r.owner ||'.'||
 10                         cur_r.table_name into l_cnt;
 11      if l_cnt > 0 then
 12         dbms_output.put_line(cur_r.owner ||'.'|| cur_r.table_name ||' = ' || l_cnt);
 13      end if;
 14    end loop;
 15  end;
 16  /
HR.EMPLOYEES = 107
SCOTT.EMP = 14

PL/SQL procedure successfully completed.

SQL>

In my databases, there are two such tables:

• one is owned by HR, its name is EMPLOYEES and contains 107 rows
• another one is owned by SCOTT, its name is EMP and contains 14 rows

I believe that you shouldn't have any problems in adjusting that code to your needs.

I'm not sure why would you want to search only through schema2; in that case, it is simpler to connect as schema2 and do the same job using user_tables (or all_tables) (so you don't have to connect as a privileged user).

Or, if you want to pick the user within that code, you'd add yet another condition into the where clause (line #6):

and owner = 'SCHEMA2'