3. SQL to Check Primary Indexes

In the SQL below, the Table name is hard-coded.
A Shell Script also exists, which takes the table name as a parameter.
  1. Find the Table Owner ...*/
    SELECT owner,count(*) FROM all_tables GROUP BY owner;
    Typical Output :-
     OWNER          COUNT(*)
    rem ------------   ----------
    rem OPS$ORACLE     64
    rem SYS            70
    rem SYSTEM         62
    rem Add check that all Primary keys are unique ...
    set heading off
    set feedback off
    SELECT   ind.uniqueness, COUNT(*)
    FROM     all_indexes ind
    WHERE    ind.table_owner = 'OPS$ORACLE'  /* TABLE_OWNER */  
    AND      ind.index_name  LIKE '%_PK'
    GROUP    BY ind.uniqueness ;
    rem Use Table Owner to avoid retrieving details of System and
    other Tables ...
    FROM     all_ind_columns col, all_indexes ind,all_tab_columns
    WHERE    ind.table_owner = 'OPS$ORACLE'  /* TABLE_OWNER */
    AND      ind.table_name  =  col.table_name 
    AND      ind.table_name  =  tab.table_name 
    AND      ind.table_name  = 'BW_CONTRACTS'  
    AND      tab.table_name  = 'BW_CONTRACTS'  
    AND      col.column_name =  tab.column_name 
    AND      ind.index_name  =  col.index_name 
    AND      ind.index_name  LIKE '%_PK'
    AND      col.index_name  LIKE '%_PK'
    ORDER    BY ind.table_name ,tab.column_id ,ind.index_name ;

    [ Home Page | Search & Site Map | What's New ]

    <!-- End of Standard Footer>