All Tables in Hierarchical Order (Tables in Parent Child order)

Here is the script that can be used to produce a nice Hierarchical View for all the tables with in the schema. (parent-children-grand children-grand grand children ... and continues).

select level, rpad( '*', (level-1)*2, '*' ) || table_name table_name

, pkey_constraint, fkey_constraint, r_constraint_name

from (select a.table_name, a.constraint_name pkey_constraint,

b.constraint_name fkey_constraint, b.r_constraint_name

from user_constraints a, user_constraints b

where a.table_name = b.table_name

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

and b.constraint_type = 'R'

union all

select table_name, constraint_name, null, null

from user_constraints

where constraint_type= 'P'

--TO COVER THE CHILDRENS WHO DONT HAVE EITHER PK OR UK

union all

select table_name, null, null, r_constraint_name

from user_constraints uc

where constraint_type = 'R'

and not exists (select 1 from user_constraints uc1

where uc1.table_name = uc.table_name

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

)

start with fkey_constraint is null

--and table_name = 'SANTHOSHCHANNA' --un-comment this line and provide particular table name that you are looking for to get it child's.

connect by nocycle prior pkey_constraint = r_constraint_name