JonLeah JonLeah - 1 month ago 12
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

max()
?

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

APC APC
Answer

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.

SQL>

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

Comments