Toon tabellen en kolommen met hun pk’s en fk’s uit een schema.

Ok ik zie dat de code hier en daar wat netter kan. Maar ‘t doet ‘t wel, voor als je wil weten hoe het hele referentiele plan is opgesteld in je schema. (Zie ook de vervolg post).

SELECT TAC.table_name
,      TAC.column_name
,      tac.data_type||' ('||CASE WHEN data_precision IS NOT NULL
                              THEN    data_precision|| CASE WHEN data_scale > 0
                                                         THEN ',' || data_scale
                                                         ELSE NULL
                                                       END
                              ELSE TO_CHAR (data_length)
                           END || ')' datatype
,      CASE WHEN tac.nullable='N'
         THEN 'NOT NULL'
       END nullability
,      CASE WHEN pk.position IS NOT NULL
         THEN '(PK)'
       END pk
,      CASE WHEN fk.constraint_name IS NOT NULL
         THEN '(FK)'
       END fk
FROM   all_tab_columns tac
LEFT OUTER JOIN (SELECT  cons.constraint_name
                 ,       cons.table_name
                 ,       conc.column_name
                 ,       conc.position
                 FROM    all_constraints cons
                   LEFT OUTER JOIN all_cons_columns conc
                     ON  cons.constraint_name = conc.constraint_name
                    AND  cons.owner = conc.owner
                   WHERE cons.owner = '<OWNER>'
                   AND   constraint_type = 'P') pk
  ON pk.column_name = tac.column_name
 AND pk.table_name = tac.table_name
LEFT OUTER JOIN (SELECT  cons.constraint_name
                 ,       cons.table_name
                 ,       conc.column_name
                 ,       conc.position
                 --,      cons.*
                 FROM    all_constraints cons
                   LEFT OUTER JOIN all_cons_columns conc
                     ON  cons.constraint_name = conc.constraint_name
                    AND  cons.owner = conc.owner
                   WHERE cons.owner = '<OWNER>'
                     AND constraint_type = 'R') fk
  ON fk.column_name = tac.column_name
 AND fk.table_name  = tac.table_name
WHERE  tac.owner   = '<OWNER>'
ORDER BY  tac.table_name
        , pk.position
        , tac.column_name