CODEWITHSUNDEEP
oracle-databaseoracle12c
Mike
Mike

Reputation: 801

Find tables with specific criteria - Oracle 12c

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:

Schema2 has tables:

Schema3 has tables:

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?

Upvotes: 0

Views: 46

Answers (2)

Salim
Salim

Reputation: 2178

Littlefoot's answer would work but may take longer. Assuming stats has been collected the following SQL should give you want you want and much faster. If stats are not fresh then it can give wrong result.

select * from DBA_TABLES where TABLE_NAME like 'NUM_%' and NUM_ROWS > 0

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142788

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'

Upvotes: 1

Related Questions