DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How To get the max(Primary key)

  1. #1
    Join Date
    Jan 2006
    Posts
    1

    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

    How can I locate this spool file on other machine.

    Any help is appreciated,
    Thanx
    Last edited by cool_tas; 01-27-2006 at 07:49 AM.

  2. #2
    Join Date
    Jan 2006
    Posts
    7

    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.

    JR



    DECLARE
    stmt varchar2(200);
    c1_rec user_constraints%ROWTYPE;
    c2_rec user_cons_columns%ROWTYPE;

    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

    -- Now build SQL to get max value for each column and output
    stmt := 'SELECT MAX(' || c2_rec.column_name || ') FROM ' || c1_rec.table_name;
    dbms_output.put_line(stmt);

    END LOOP;

    END LOOP;
    END;

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    -- Since a primary key can be composed of
    -- multiple columns, build SQL for each one
    Who told you ?
    Or
    Where did you read it?


    Tamil

  4. #4
    Join Date
    Jan 2006
    Posts
    7
    Lots of examples in Oracle Doc on creating a multicolumn primary key constraint, just check the master index.

    Here is an example:

    SQL> create table test1 (col1 number, col2 number);

    Table created.

    SQL> alter table test1 ADD (CONSTRAINT test1_pk PRIMARY KEY (col1,col2));

    Table altered.

    SQL> select table_name, constraint_type from user_constraints where constraint_name = 'TEST1_PK';

    TABLE_NAME C
    ------------------------------ -
    TEST1 P

    SQL> select * from user_cons_columns where constraint_name = 'TEST1_PK';

    OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
    ---------- ------------------ ---------- ------------ ----------
    SCOTT TEST1_PK TEST1 COL1 1
    SCOTT TEST1_PK TEST1 COL2 2

    SQL>

    JR

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    And how this code helps?
    -- Now build SQL to get max value for each column and output
    stmt := 'SELECT MAX(' || c2_rec.column_name || ') FROM ' || c1_rec.table_name;
    dbms_output.put_line(stmt);

    You consider only one column.

    Tamil

  6. #6
    Join Date
    Jan 2006
    Posts
    7
    Using table created in example above, this is what I get when I run the code...

    SQL> set serveroutput on
    SQL> /
    SELECT MAX(DEPTNO) FROM DEPT
    SELECT MAX(EMPNO) FROM EMP
    SELECT MAX(COL1) FROM TEST1
    SELECT MAX(COL2) FROM TEST1


    PL/SQL procedure successfully completed.

    SQL>

    There are two loops in the procedure, one to step through the PK constraints, and using the constraint name found we then loop through all the columns in the constraint generating the SQL he wants.

    JR

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You did not get what I am indirectly saying.
    When a table has a PK that has 2 columns, your SQL will not work or will give wrong result.

    Let us say PK is on 2 columns - both are varchar2. The SQL I expected:
    Select max(pk_col_1), max(pk_col_2) from my_table.
    OR
    select max(pk_col_1||pk_col_2) from my_table.

    And so on ....

    Tamil

  8. #8
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by tamilselvan
    You did not get what I am indirectly saying.
    When a table has a PK that has 2 columns, your SQL will not work or will give wrong result.

    Let us say PK is on 2 columns - both are varchar2. The SQL I expected:
    Select max(pk_col_1), max(pk_col_2) from my_table.
    OR
    select max(pk_col_1||pk_col_2) from my_table.

    And so on ....

    Tamil
    It will not create as you have listed but it will do as:

    select max(pk1) table1
    select max(pk2) table1
    select max(pk1) table2
    select max(pk2) table2
    select max(pk3) table2
    "What is past is PROLOGUE"

  9. #9
    Join Date
    Jan 2006
    Posts
    7
    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.

    JR

  10. #10
    Join Date
    Sep 2013
    Posts
    1
    set SERVEROUTPUT on;
    set feedback off;
    declare
    max_cnt number;
    begin

    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;

    DBMS_OUTPUT.PUT_LINE(TAB_CUR.TABLE_NAME||'-'||MAX_CNT);

    end LOOP;
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width