Table relations by foreign keys

L

Let's explore table references by traversing foreign keys. First show all foreign keys for all tables (including multi columns keys).

WITH t (table_name, referenced_table, pk_name, fk_name) AS (
    SELECT DISTINCT
        r.table_name,
        p.table_name            AS referenced_table,
        p.constraint_name       AS pk_name,
        r.constraint_name       AS fk_name
    FROM user_constraints r
    JOIN user_constraints p
        ON r.r_constraint_name  = p.constraint_name
        AND r.constraint_type   = 'R'
    WHERE p.constraint_type     = 'P'
    UNION ALL
    SELECT NULL, t.table_name, NULL, NULL
    FROM user_tables t
    ORDER BY 1, 2
),
c AS (
    SELECT
        r.constraint_name,
        LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols
    FROM user_cons_columns c
    JOIN user_constraints r
        ON r.constraint_name    = c.constraint_name
    WHERE r.constraint_type     = 'R'
    GROUP BY r.constraint_name
)
SELECT
    LPAD(' ', (LEVEL - 1) * 2) || t.referenced_table AS table_name,
    t.fk_name,
    c.cols,
    t.pk_name
FROM t
LEFT JOIN c ON c.constraint_name = t.fk_name
CONNECT BY NOCYCLE PRIOR t.referenced_table = t.table_name
START WITH t.table_name IS NULL
ORDER SIBLINGS BY t.table_name;

For small schemas you can also try this:

SELECT p.table_name, LISTAGG(r.table_name, ', ') WITHIN GROUP (ORDER BY r.table_name) AS referred_tables
FROM user_constraints r
JOIN user_constraints p
    ON p.r_constraint_name  = r.constraint_name
    AND p.constraint_type   = 'R'
WHERE r.constraint_type     = 'P'
GROUP BY p.table_name
ORDER BY p.table_name;

Now let's try the opposite. Select all tables with referenced tables (and columns).

WITH c AS (
    SELECT
        r.constraint_name,
        LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols
    FROM user_cons_columns c
    JOIN user_constraints r
        ON r.constraint_name    = c.constraint_name
    WHERE r.constraint_type     = 'R'
    GROUP BY r.constraint_name
)
SELECT
    CASE WHEN t.referenced_table IS NULL
        THEN t.table_name
        ELSE '  ' || t.referenced_table END AS table_name,
    t.fk_name,
    c.cols
FROM (
    SELECT DISTINCT
        p.table_name,
        r.table_name            AS referenced_table,
        r.constraint_name       AS fk_name
    FROM user_constraints r
    JOIN user_constraints p
        ON p.constraint_name    = r.r_constraint_name
        AND p.constraint_type   = 'P'
    WHERE r.constraint_type     = 'R'
    UNION ALL
    SELECT t.table_name, NULL, NULL
    FROM user_tables t
) t
LEFT JOIN c ON c.constraint_name = t.fk_name
ORDER BY t.table_name, t.referenced_table NULLS FIRST;

For small schemas you can also try this:

SELECT
    p.table_name,
    LISTAGG(r.table_name, ', ') WITHIN GROUP (ORDER BY r.table_name) AS references
FROM user_constraints r
JOIN user_constraints p
    ON p.constraint_name    = r.r_constraint_name
    AND p.constraint_type   = 'P'
WHERE r.constraint_type     = 'R'
GROUP BY p.table_name
ORDER BY p.table_name;