Getting child tables, referencing columns and referred columns for a parent table

Few days ago during a task at my office, I needed to get child tables of few given tables, and which columns of child tables are referencing parent tables. Another thing I observed is, some parent tables contain composite primary and/or unique keys. Also, some child tables refer both primary key and unique key columns.

For example, if I have parent and child tables created like below,

create table parent_table(
  c1 number,

  c2 number,

  c3 number,

  constraint pk_parent_table

    primary key  (c1), 
  constraint u1_parent_table 
    unique (c2, c3)

);

create table child_table1( 
  cc1 number,

  constraint child_table1_fk1

    foreign key (cc1) references parent_table(c1)

);

create table child_table2(
  cc2 number,

  cc3 number,

  constraint child_table2_fk1

    foreign key (cc2, cc3) references parent_table(c2, c3)

);

create table child_table3(
  cc1 number,

  cc2 number,

  cc3 number,

  constraint child_table3_fk1

    foreign key (cc1) references parent_table(c1),

  constraint child_table3_fk2

    foreign key (cc3, cc2) references parent_table(c2, c3)

);

Observe the order of columns in foreign key constraint referencing composite unique key of parent

child_table2: foreign key (cc2, cc3) references parent_table(c2, c3)

child_table3: foreign key (cc3, cc2) references parent_table(c2, c3)

The task is to get all child tables of 'PARENT_TABLE' and which columns of child tables are referencing which columns of parent. The output should look like this,

TABLE_NAME     REFERENCING_COLUMNS  REFERENCED_COLUMNS

-------------- -------------------- -------------------

CHILD_TABLE1   CC1                  C1

CHILD_TABLE2   CC2,CC3              C2,C3

CHILD_TABLE3   CC1                  C1

CHILD_TABLE3   CC3,CC2              C2,C3

I could not get any ready-made SQL for this on net. So, started writing a new query. The query which gave almost the results I needed was

select  child_tab_cols.owner,

        child_tab_cols.table_name,

        child_tab_cols.column_name referencing_columns,

        parent_tab_cols.column_name referenced_columns

from    all_constraints   parent_tab_cons,

        all_cons_columns  parent_tab_cols,

        all_constraints   child_tab_cons,

        all_cons_columns  child_tab_cols

where    

-- Filters to get primary/unique constraint names

        parent_tab_cons.owner            = 'MURTY'

    and parent_tab_cons.table_name       = 'PARENT_TABLE'

    and parent_tab_cons.constraint_type in ('P', 'U')

-- Get columns of parent table using primary/unique constraint names

    and parent_tab_cols.constraint_name  = parent_tab_cons.constraint_name

-- Get child's foreign constraint names that refer parent's primary/unique constraints

    and child_tab_cons.r_constraint_name = parent_tab_cons.constraint_name

-- Get columns of child table using foreign constraint names

    and child_tab_cols.constraint_name   = child_tab_cons.constraint_name 

-- Now match columns of parent and child in case of composite primary/unique key

    and child_tab_cols.position          = parent_tab_cols.position

-- group to aggregate column names

order by  child_tab_cols.owner,

          child_tab_cols.table_name,

          child_tab_cols.constraint_name,

          child_tab_cols.position

;

Output of the query is

OWNER   TABLE_NAME    REFERENCING_COLUMNS  REFERENCED_COLUMNS

------- ------------- -------------------- -------------------

MURTY   CHILD_TABLE1  CC1                  C1

MURTY   CHILD_TABLE2  CC2                  C2

MURTY   CHILD_TABLE2  CC3                  C3

MURTY   CHILD_TABLE3  CC1                  C1

MURTY   CHILD_TABLE3  CC3                  C2

MURTY   CHILD_TABLE3  CC2                  C3

I have added owner name in the query as we had child tables even in different schema also. Though this query gives the required information, I wanted to aggregate composite primary/unique columns in output by separating column names with ','.

To use my favorite string aggregation technique, I have created required data type and function.

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION to_string (

                  nt_in        IN varchar2_ntt,

                  delimiter_in IN VARCHAR2 DEFAULT ','

                  ) RETURN VARCHAR2 IS

     v_idx PLS_INTEGER;

     v_str VARCHAR2(32767);

     v_dlm VARCHAR2(10);

BEGIN

     v_idx := nt_in.FIRST;

     WHILE v_idx IS NOT NULL LOOP

        v_str := v_str || v_dlm || nt_in(v_idx);

        v_dlm := delimiter_in;

        v_idx := nt_in.NEXT(v_idx);

     END LOOP;

     RETURN v_str;

END to_string;

/

Now using the above function, query which gives results exactly I need is

select  child_tab_cols.owner,

        child_tab_cols.table_name,

-- aggregate child table columns that refer composite primary/unique key

        to_string (cast

                    (collect (child_tab_cols.column_name order by child_tab_cols.position)

                   as varchar2_ntt )

                  ) referencing_columns,

-- aggregate parent table columns that have composite primary/unique key

        to_string (cast

                    (collect (parent_tab_cols.column_name order by parent_tab_cols.position)

                   as varchar2_ntt )

                  ) referenced_columns

from    all_constraints   parent_tab_cons,

        all_cons_columns  parent_tab_cols,

        all_constraints   child_tab_cons,

        all_cons_columns  child_tab_cols

where    

-- Filters to get primary/unique constraint names

          parent_tab_cons.owner            = 'MURTY'

      and parent_tab_cons.table_name       = 'PARENT_TABLE'

      and parent_tab_cons.constraint_type in ('P', 'U')

-- Get columns of parent table using primary/unique constraint names

      and parent_tab_cols.constraint_name  = parent_tab_cons.constraint_name

-- Get child's foreign constraint names that refer parent's primary/unique constraints

      and child_tab_cons.r_constraint_name = parent_tab_cons.constraint_name

-- Get columns of child table using foreign constraint names

      and child_tab_cols.constraint_name   = child_tab_cons.constraint_name 

-- Now match columns of parent and child in case of composite primary/unique key

      and child_tab_cols.position          = parent_tab_cols.position

-- group to aggregate column names

group by  child_tab_cols.owner,

          child_tab_cols.table_name,

          child_tab_cols.constraint_name

;

BTW, in 11gR2 we can write above query using "listagg" for aggregation like below

select  child_tab_cols.owner,

        child_tab_cols.table_name,

-- aggregate child table columns that refer composite primary/unique key

        listagg(child_tab_cols.column_name, ',')

          within group (order by child_tab_cols.position) referencing_columns,

-- aggregate parent table columns that have composite primary/unique key

        listagg(parent_tab_cols.column_name, ',')

          within group (order by parent_tab_cols.position) referenced_columns

from    all_constraints   parent_tab_cons,

        all_cons_columns  parent_tab_cols,

        all_constraints   child_tab_cons,

        all_cons_columns  child_tab_cols

where    

-- Filters to get primary/unique constraint names

          parent_tab_cons.owner            = 'MURTY'

      and parent_tab_cons.table_name       = 'PARENT_TABLE'

      and parent_tab_cons.constraint_type in ('P', 'U')

-- Get columns of parent table using primary/unique constraint names

      and parent_tab_cols.constraint_name  = parent_tab_cons.constraint_name

-- Get child's foreign constraint names that refer parent's primary/unique constraints

      and child_tab_cons.r_constraint_name = parent_tab_cons.constraint_name

-- Get columns of child table using foreign constraint names

      and child_tab_cols.constraint_name   = child_tab_cons.constraint_name 

-- Now match columns of parent and child in case of composite primary/unique key

      and child_tab_cols.position          = parent_tab_cols.position

-- group to aggregate column names

group by  child_tab_cols.owner,

          child_tab_cols.table_name,

          child_tab_cols.constraint_name

;