Help Required in Query

[sumanroyc]

Hi All,

We have a requirement to find out the relationships among tables in a particular schema and to arrange the table names from parent table to child table. That is the table (say A) which is referring to no other table should come first and the table (say B) which refers table A should come after A.

Please let me know how this ordered list of tables can be obtained from the oracle data dictionary. We have to do this using a SQl query.

Thanks and Regards,
Suman

[pragatiswain]

Hi Suman,
I don't have a Oracle DB right now. So, I have not tested the following. Still I have commented all the queries for better understanding and hope this will help you.

USER_CONSTRAINTS view contains CONSTRAINT_TYPE (R -> Foreign key, P->Primary Key)
If CONSTRAINT_TYPE = 'R',R_CONSTRAINT_NAME contains the corresponding Primary Key Name

-- GIVES THE RESULT 'TABLES ONLY WITH PRIMARY KEYS'
SELECT TABLE_NAME, 'P' KEY_FLAG FROM
(SELECT TABLE_NAME,
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME IN (
SELECT R_CONSTRAINT_NAME PK_KEY
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R')
MINUS
SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R')
UNION ALL
-- GIVES THE RESULT 'TABLES WITH PRIMARY KEYS AND FOREIGN KEYS'
SELECT TABLE_NAME, 'B' KEY_FLAG FROM
(SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME IN (
SELECT R_CONSTRAINT_NAME PK_KEY
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R')
INTERSECT
SELECT DISTINCT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R')
UNION ALL
-- GIVES THE RESULT 'OTHER TABLES'
-- SELECT ALL TABLES FROM USER_TABLES MINUS ABOVE CASES
SELECT TABLE_NAME, 'O' KEY_FLAG FROM
(
SELECT TABLE_NAME FROM USER_TABLES
MINUS
(
SELECT TABLE_NAME FROM
(SELECT TABLE_NAME,
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME IN (
SELECT R_CONSTRAINT_NAME PK_KEY
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R')
MINUS
SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R')
UNION ALL
SELECT TABLE_NAME FROM
(SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME IN (
SELECT R_CONSTRAINT_NAME PK_KEY
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R')
INTERSECTION
SELECT DISTINCT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R')
)
)

Anyone having better ideas, please post.

[suvam]

u may try with a Query using the Connect by---Prior with clause .