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