i need to get information about relationship of tables in following format:
source_table source_pk source_fk destination_table destination_pk
............ ......... ......... ................. ..............
employees employee_id job_id jobs job_id
it says table 'employees' with primary key of 'employee_id' and foreign key of 'job_id' refer to table 'jobs' with primary key of 'job_id'. is there any query to give me this information? update: i want a query that fill this table with all relationships of a particular schema like 'hr'.
You can query any of dba_constraints, all_constraints or user_constraints views based on your access level as follows
WITH tab AS (SELECT table_name,
MIN(decode(constraint_type, 'P', constraint_name, NULL)) PRIMARY_KEY,
MIN(decode(constraint_type, 'R', constraint_name, NULL)) FOREIGN_KEY,
MIN(decode(constraint_type, 'R', r_constraint_name, NULL)) DESTINATION_PK
FROM dba_Constraints
WHERE table_name = 'EMPLOYEE'
GROUP BY table_name)
SELECT t.table_name, t.PRIMARY_KEY, t.FOREIGN_KEY, uc.table_name, t.DESTINATION_PK
FROM tab t
LEFT OUTER JOIN dba_Constraints uc
ON (uc.constraint_name = t.destination_pk);
To get information about all tables in particular schema(example HR):
WITH tab AS (SELECT table_name,
MIN(decode(constraint_type, 'P', constraint_name, NULL)) PRIMARY_KEY,
MIN(decode(constraint_type, 'R', constraint_name, NULL)) FOREIGN_KEY,
MIN(decode(constraint_type, 'R', r_constraint_name, NULL)) DESTINATION_PK
FROM all_constraints
WHERE owner = 'HR'
GROUP BY table_name)
SELECT t.table_name, t.PRIMARY_KEY, t.FOREIGN_KEY, uc.table_name, t.DESTINATION_PK
FROM tab t
LEFT OUTER JOIN all_constraints uc
ON (uc.constraint_name = t.destination_pk)
ORDER BY 1;
Note: If there are no primary key on a table, no rows will be returned.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments