Re: Tables and PK

From: Peter Schneider <pschneider.ctj_at_metronet.de>
Date: 1997/12/30
Message-ID: <34a987b9.33517902_at_pop-news.metronet.de>#1/1

On Tue, 30 Dec 1997 15:33:18 GMT, "Mike Kennedy" <mike.kennedy_at_mci.com> wrote:

>Hi everyone...I've wrestled with this for a while and can't seem to get the
>correct query. I'm trying to write a query to return the table names and
>the columns (in order) which comprise the primary keys (any constraints),
>indented. For example:
>
>Tableone
> Col1 PK
> Col2 PK
> Col3
> Col4
> Col5 FK
>
>Tabletwo.....etc
>
>Any help would be appreciated....
>
>--Mike Kennedy
>
>

Hi Mike,

try the following script and see if you like it.

There's no online help in SQL*Plus (Oracle obviously thinks nobody needs it as that program has been out for so long time :-) but you can find explanations of all those formatting commands in the 'SQL*Plus Users Guide and Reference' doc.

HTH, Peter

---

CLEAR COLUMNS
CLEAR BREAKS
SET PAGESIZE 66
SET HEADING ON
COLUMN table_name NEW_VALUE tb_name NOPRINT TTITLE LEFT 'Table Name: ' tb_name SKIP 2 BREAK ON table_name SKIP PAGE ON constraint_name ON type COLUMN constraint_name FORMAT a30

COLUMN type            FORMAT a7
COLUMN column_name     FORMAT a30
SELECT uc.table_name,
       uc.constraint_name,
       DECODE (uc.constraint_type,
               'P', 'Primary',
               'U', 'Unique',
               'R', 'Foreign',
               'C', 'Check',
               '???') type,
       ucc.column_name
  FROM user_cons_columns ucc,
       user_constraints uc
 WHERE uc.table_name LIKE UPPER('&Table')

   AND uc.constraint_type LIKE UPPER('&Type')    AND uc.constraint_name = ucc.constraint_name  ORDER BY uc.table_name,

       DECODE (uc.constraint_type,
               'P', 1,
               'U', 2,
               'R', 3,
               'C', 4,
               5),
       uc.constraint_name, ucc.position;

CLEAR COLUMNS
CLEAR BREAKS

---

-- 
Peter Schneider
pschneider.ctj_at_metronet.de

Received on Tue Dec 30 1997 - 00:00:00 CET