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 ','.
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
;
