Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: more problems with listing pks and fks

Re: more problems with listing pks and fks

From: Patrick Flahan <flahan_at_earthlink.net>
Date: 1998/09/16
Message-ID: <6tpic5$9ib$1@ash.prod.itd.earthlink.net> 






Here is a copy of a quick little script that I wrote to give information on
a table.  It might be something that will be of use to you or others.  If
anyone has something similar or better I would like to see it.




Patrick


flahan_at_earthlink.net




you can copy this into a file and call from sql*plus.



You would call the script like this:


@table <owner>.<table_name>


@table scott.stocks



---Start of script


SET SCAN ON



SET VERIFY OFF



DEFINE pOwnerDotTableName = &1



set serveroutput on size 1000000


DECLARE



/*


Things to add:


 Constraints --


 Foreign Keys


 Triggers


*/


  cStage              VARCHAR2(200) := 'Initial Declare';
  cOwnerDotTableName  VARCHAR2(100) := UPPER('&pOwnerDotTableName');
  cOwner              VARCHAR2(40)  :=
SUBSTR(cOwnerDotTableName,1,(INSTR(cOwnerDotTableName,'.')-1) );
  cTableName          VARCHAR2(40)  :=




SUBSTR(cOwnerDotTableName,(INSTR(cOwnerDotTableName,'.')+1) );


  cIndexString        VARCHAR2(1000);
  cSize               VARCHAR2(20);
  cNullable           VARCHAR2(10);
  cTemp1              VARCHAR2(1);

  cNoIndexesFound     VARCHAR2(100) := 'No indexes were found for this


table';


  cNoConstraintsFound VARCHAR2(100) := 'No constraints were found for this
table';


  cTableDoesNotExist  VARCHAR2(100);


  cConstraintType     VARCHAR2(20);


  bPrintColumns       BOOLEAN := TRUE;


  --bPrintColumns       BOOLEAN := FALSE;






  nLengthOfTableName  NUMBER := LENGTH(cOwnerDotTableName) + 8;



  --Programmer Defined Exceptions



  eTableNameFormattedWrong EXCEPTION;


  eExitTakeNoAction        EXCEPTION;





  CURSOR curAllTables(pcTableName VARCHAR2

                     ,pcOwner     VARCHAR2)


    IS


    SELECT *


      FROM all_tables
     WHERE table_name = pcTableName
       AND owner = pcOwner;

  CURSOR curAllTabColumns(pcTableName VARCHAR2,
                          pcOwner     VARCHAR2)


    IS


    SELECT *


      FROM all_tab_columns
     WHERE table_name = pcTableName
       AND owner = pcOwner
     ORDER BY column_id;

  CURSOR curAllIndexes(pcTableName VARCHAR2
                      ,pcOwner     VARCHAR2)


    IS


    SELECT *


      FROM all_indexes
     WHERE table_name = pcTableName
       AND table_owner = pcOwner
     ORDER BY uniqueness DESC, index_name ASC;

  CURSOR curAllIndColumns(pcTableName VARCHAR2
                         ,pcOwner     VARCHAR2
                         ,pcIndexName VARCHAR2)


   IS


    SELECT *


      FROM all_ind_columns
     WHERE index_name = pcIndexName
       AND table_owner = pcOwner
       AND table_name = pcTableName
     ORDER BY column_position DESC;

  CURSOR curAllConstraints(pcTableName VARCHAR2
                          ,pcOwner     VARCHAR2)


    IS


    SELECT *


      FROM all_constraints
     WHERE table_name = pcTableName
       AND owner = pcOwner
     ORDER BY DECODE(constraint_type,'P',1
                                    ,'U',2
                                    ,'R',3
                                    ,'C',4
                                    ,'V',5,6)
              ,constraint_name;





  CURSOR curAllUsers(pcOwner VARCHAR2)


   IS


    SELECT 'x'

      FROM all_users
     WHERE username = pcOwner;

----------------------------------------------------------------


BEGIN



  cStage := 'Start of Program';


  --Display the owner.table_name that was passed in


  DBMS_OUTPUT.PUT_LINE(RPAD('-',nLengthOfTableName,'-'));
  DBMS_OUTPUT.PUT_LINE('--  '||cOwnerDotTableName||'  --');
  DBMS_OUTPUT.PUT_LINE(RPAD('-',nLengthOfTableName,'-'));





  --Verify that the owner.name is in correct format and that






      RAISE eExitTakeNoAction;


    END IF;



    CLOSE curAllUsers;


  END IF;


  DBMS_OUTPUT.PUT_LINE('-');


  cTableDoesNotExist := 'Verify table name '''||cTableName||''' is correct';



  cStage := 'Open curAllTables';


  FOR recAllTabs IN curAllTables(cTableName,cOwner)
  LOOP



    cTableDoesNotExist := NULL;


    DBMS_OUTPUT.PUT_LINE('Table:      '||recAllTabs.table_name||'  Owner:
'||recAllTabs.owner);


    DBMS_OUTPUT.PUT_LINE('Tablespace: '||recAllTabs.tablespace_name);



    IF bPrintColumns


    THEN



      DBMS_OUTPUT.PUT_LINE('-');



      cStage := 'Open curAllTabColumns';
      DBMS_OUTPUT.PUT_LINE('Column                                   Data


Type');


DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
      FOR recAllTabCols IN curAllTabColumns(recAllTabs.table_name,
recAllTabs.owner)
      LOOP
        IF recAllTabCols.data_type IN ('VARCHAR2','CHAR')
        THEN
          cSize := '('||recAllTabCols.data_length||')';
        ELSIF recAllTabCols.data_type = 'NUMBER'
        THEN
          IF recAllTabCols.data_precision IS NULL
          THEN
            cSize := NULL;
          ELSE
            IF recAllTabCols.data_scale IS NULL
            THEN
              cSize := '('||recAllTabCols.data_precision||')';
            ELSE
              cSize :=
'('||recAllTabCols.data_precision||','||recAllTabCols.data_scale||')';
            END IF;
          END IF;
        END IF;
        IF recAllTabCols.nullable = 'N'
        THEN
          cNullable := 'NOT NULL';
        ELSE
          cNullable := ' ';
        END IF;






DBMS_OUTPUT.PUT_LINE(rpad(recAllTabCols.column_name,31)||RPAD(cNullable,10)|
|recAllTabCols.data_type||cSize);



      END LOOP;



    END IF;


    DBMS_OUTPUT.PUT_LINE('-');


    cStage := 'Open curAllIndexes';


    DBMS_OUTPUT.PUT_LINE(RPAD('Index',30)||'Uniqueness   Columns');





DBMS_OUTPUT.PUT_LINE('----------------------------- ------------ -----------
--------------------------');




    FOR recAllInds IN curAllIndexes(recAllTabs.table_name, recAllTabs.owner)
    LOOP


      --set this to null so message will not print
      cNoIndexesFound := NULL;

      cStage := 'Open curAllIndColumns';
      cIndexString := NULL;
      FOR recAllIndCols IN curAllIndColumns(recAllTabs.table_name
                                           ,recAllTabs.owner
                                           ,recAllInds.index_name)
      LOOP
        cIndexString := recAllIndCols.column_name ||', '||cIndexString;
      END LOOP;

      cIndexString := RTRIM(cIndexString,', ');





DBMS_OUTPUT.PUT_LINE(RPAD(recAllInds.index_name,30)||rpad(recAllInds.uniquen
ess,13)|| cIndexString);


    END LOOP;


    --IF cNoIndexesFound is null nothing will print
    DBMS_OUTPUT.PUT_LINE(cNoIndexesFound);



    DBMS_OUTPUT.PUT_LINE('-');


    cStage := 'Open curAllConstraints';


    DBMS_OUTPUT.PUT_LINE(RPAD('Constraint Name',30) ||' Status    Type');






DBMS_OUTPUT.PUT_LINE(RPAD('-',30,'-')||RPAD(' -',10,'-')||RPAD(' -',15,'-'))

;


    FOR recAllCons IN curAllConstraints(recAllTabs.table_name

                                       ,recAllTabs.owner)
    LOOP
      cStage := 'Get Constraint Type';
      cNoConstraintsFound := NULL; --set this to null so message will not
print
      IF recAllCons.constraint_type = 'C'
      THEN
        cConstraintType := 'Check';
      ELSIF recAllCons.constraint_type = 'R'
      THEN
        cConstraintType := 'Foreign Key';
      ELSIF recAllCons.constraint_type = 'P'
      THEN
        cConstraintType := 'Primary Key';
      ELSIF recAllCons.constraint_type = 'U'
      THEN
        cConstraintType := 'Unique';
      ELSIF recAllCons.constraint_type = 'V'
      THEN
        cConstraintType := 'With Check Option';
      ELSE
        cConstraintType := recAllCons.constraint_type;
      END IF;






DBMS_OUTPUT.PUT_LINE(rpad(recAllCons.constraint_name,31)||recAllCons.status
|| '   '||cConstraintType);



    END LOOP;


    --If cNoConstraintsFound is null nothing will print
    DBMS_OUTPUT.PUT_LINE(cNoConstraintsFound);



  END LOOP;


  DBMS_OUTPUT.PUT_LINE(cTableDoesNotExist);--this will not print if
cTableDoesNotExist is null



EXCEPTION



  WHEN eTableNameFormattedWrong THEN


    DBMS_OUTPUT.PUT_LINE('Please verify table name '''||cOwnerDotTableName
||''' is in the correct format.  <owner>.<table name>');
  WHEN eExitTakeNoAction THEN


    NULL;--Take no action


  WHEN OTHERS THEN



    DBMS_OUTPUT.PUT_LINE('Error '||SQLERRM|| ' occurred at '||cStage);
END;



/


UNDEFINE pOwnerDotTableName


UNDEFINE 1



SET VERIFY ON



---End of Script










Thenardier_at_POBoxes.com wrote in message <6tnimn$ugb$1_at_nnrp1.dejanews.com>...

>
>
>hi,
>
>i asked u people about how to list pks and fks of a tables and
>someone told me to select table name from the all_constraints
>where constraint_type is 'P'.  i tried it and find it works fine
>if the table's pk contains only one columns.  but if the pk is
>a combination of different columns, it returns no row.  what
>should i do?
>
>thanx.
>
>rgds,
>thenard
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum


Received on Wed Sep 16 1998 - 00:00:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US