If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

How To get the max(Primary key)

Hi all,

I have a requirement where I need to get the max(primary key ) of all tables in database.
I have an option to manually write a query like:
select max(primary_key1) from table1;
select max(primary_key2) from table2;

But I want to avoid doing that.
Do we have any better way of doing it.

Also I need to create a spool file on some other machine, whose IP is known.
Generally when we spool a file we write:
spool c:/Records_Count/STAGING_TABLE_COUNT

procedure

Here is an example procedure to generate SQL to find the maximum value for every column in all the primary keys in your current schema. If you want this for every schema replace the references to USER_ views with DBA_ . In that case you will probably want to filter out SYS owned tables etc.

So you could SET SERVEROUTPUT ON, set a spool file, then run the below procedure. Edit the spool file to get rid of the junk lines then execute it.

BEGIN
FOR c1_rec IN
(select * from user_constraints where constraint_type = 'P')
LOOP
-- Since a primary key can be composed of
-- multiple columns, build SQL for each one
FOR c2_rec IN
(SELECT COLUMN_NAME FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = c1_rec.constraint_name
ORDER BY POSITION)
LOOP

The procedure was meant as an example to the original poster on how to do this, not to give them a procedure that handles every possible situation. It works fine for single column PKs which may be all they need. Generating individual selects for each column in the primary key was probably not the best example, but it showed them how to loop through the constraint column view to get each column in the PK.

for TAB_CUR in (select C.TABLE_NAME,CC.COLUMN_NAME
from ALL_TAB_COLUMNS TC,ALL_CONSTRAINTS C,ALL_CONS_COLUMNS CC
where tc.owner=C.OWNER
and C.OWNER=CC.OWNER
and tc.table_name=c.table_name
and C.TABLE_NAME=CC.TABLE_NAME
and tc.COLUMN_NAME=cc.COLUMN_NAME
and C.OWNER='USER'
and C.CONSTRAINT_TYPE='P'
and tc.DATA_TYPE='NUMBER'
and rownum<=10)
LOOP
max_cnt:=0;
execute immediate 'select max('||tab_cur.column_name||') from '|| tab_cur.table_name into max_cnt;