CODEWITHSUNDEEP
databaseoracle-database
Kiran Joshi
Kiran Joshi

Reputation: 746

Query to get parent tables and then child tables in a database schema

I am writing a dbExporter using JDBC connections which will export data from one database to another database. For this I would need what are the tables to be exported, I am getting all the table names using a query,

SELECT DISTINCT OBJECT_NAME 
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

But the problem would be the order in which they are being fetched,

Scenario : (ORDER_DETAILS has foreign key from ORDERS). Let say the result of above query would result in ORDER_DETAILS, ORDERS. I would try to export the data of ORDER_DETAILS first and then ORDERS. This step throws exception as ORDER_DETAILS has a foreign key relationship with ORDERS. Hence I need to export ORDERS table first.

How should I make sure the results of above query gets parent table first and then its child tables. Is there a way directly from query which will return me the tables in order.

Upvotes: 1

Views: 3047

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

You could use system views user_constraints and user_cons_columns here, next build hierarchical query showing dependencies between tables and join it to user_tables:

with hierarchy as (
  select child_table, max(level) lvl 
    from (
      select uc.table_name child_table, ucc.table_name parent_table
        from user_constraints uc
        left join user_cons_columns ucc on ucc.constraint_name = uc.r_constraint_name
        where constraint_type = 'R')
    connect by parent_table = prior child_table
    group by child_table)
select u.table_name, h.lvl 
  from user_tables u left join hierarchy h on h.child_table = u.table_name
  order by lvl nulls first, u.table_name

Output and SQLFiddle demo:

TABLE_NAME                            LVL
------------------------------ ----------
ORDERS                         
TEST_TABLE                     
ORDER_DETAILS                           1
ORDER_SUB_DETAILS                       2

At first you have to export tables with nullable LVL, rest in order by this column. I'm not sure if this solves every possible dependiences or more complicated scenarios but works for given example.

Upvotes: 1

Related Questions