Re: SQL Statement

From: Rene van Geijn <rene.van.geijn_at_brunel.nl>
Date: Thu, 03 Sep 1998 07:49:03 +0200
Message-ID: <35EE2DCF.36491833_at_brunel.nl>

This Works
:
SELECT T.Table_Name,

       TC.Column_Name,
       C.Constraint_Name,
       C.Constraint_Type
FROM   All_Tab_Columns  TC,
       All_Cons_Columns CC,
       All_Constraints  C,
       All_Tables       T
WHERE  T.Table_name      = TC.table_name
AND    TC.Column_Name    = CC.Column_Name
AND    C.Constraint_Name = CC.Constraint_Name
AND    T.Table_name      = C.Table_Name

AND C.Constraint_Type = 'P'
UNION
SELECT T.Table_Name,

       TC.Column_Name,
       '-',
       '-'
FROM   All_Tab_Columns  TC,
       All_Tables       T
WHERE  T.Table_name      = TC.table_name
AND    NOT EXISTS
       ( SELECT 1
         FROM   All_Cons_Columns CC,
                All_Constraints  C
         WHERE  C.Constraint_name = CC.Constraint_name
         AND    C.Table_name      = T.Table_Name
         AND    C.Constraint_Type = 'P'
         AND    CC.Column_Name    = TC.Column_Name )

ORDER BY 1,3,2 Alexandr Reshitko wrote:

> Hi,
>
> How can I write a statement that returns list of all columns in a database
> and if any column a primary key or not.
> I trying:
>
> SELECT Sys.All_Tab_Columns.Table_Name,
> Sys.All_Tab_Columns.Column_Name,
> Sys.All_Cons_Columns.Constraint_Name,
> Sys.All_Constraints.Constraint_Type
> FROM Sys.All_Tab_Columns,
> Sys.All_Cons_Columns,
> Sys.All_Constraints
> WHERE Sys.All_Tab_Columns.Column_Name = Sys.All_Cons_Columns.Column_Name
> (+) AND
> Sys.All_Constraints.Constraint_Name (+) =
> Sys.All_Cons_Columns.Constraint_Name AND
> Sys.All_Constraints.Constraint_Type (+) = 'P'
>
> but its works wrong because its list some columns twice or more
> (some different constraints refers to the same columns).
>
> So, how can I limit this statement and except columns reduplication.
> Or may be exists an other idea?
>
> Thanks in advance.
Received on Thu Sep 03 1998 - 07:49:03 CEST