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
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.