JonLeah JonLeah - 1 year ago 75
SQL Question

Oracle SQL Find Highest ID Across All Tables

Every one of my tables has an "id" field. I need to be able to find the highest ID across all of them, given that the list of tables might change.

Is there any way to get the list of tables in an oracle database, aggregate their rows (only ids), and then get the


P.S. This is for updating a sequence which has gone out of whack.

Answer Source

Here is some simple dynamic SQL driving off the data dictionary:

SQL> set serveroutput on
SQL> declare
  2      l_id pls_integer;
  3      max_id pls_integer;
  4      max_tab_name varchar2(30);
  5  begin
  6      max_id := 0;
  7      for r in ( select table_name
  8                 from user_tab_columns
  9                 where column_name = 'ID' )
 10      loop
 11          execute immediate 'select max(id) from '||r.table_name
 12              into l_id;
 13          if l_id > max_id
 14          then
 15              max_id := l_id;
 16              max_tab_name := r.table_name;
 17          end if;
 18      end loop;
 19      dbms_output.put_line('Highest score = '||max_id||' table='||max_tab_name);
 20  end;
 21  /
Highest score = 2010070705 table=SESSIONS

PL/SQL procedure successfully completed.


If the sequence services tables across several schemas, you will need to drive off ALL_TAB_COLUMNS and include OWNER in the query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download