Oracle all foreign key references

Question

I've inherited a schema so don't have complete knowledge/confidence in what is there.

I have a project table with a projectId. There are a whole bunch of other tables that reference this table by project id. What I want to do is run a query to establish.

1) Which tables have foreign key references to the project table on the project id 2) Which tables have a column called project id (in case foreign keys are not set up).

If it was sql server i know how to query the meta data but how do I do this in oracle?

Answer 1

1): SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME='R' and R_CONSTRAINT_NAME='xxx'

where xxx is the name of the primary key constraint on the project table

2): SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='PROJECT_ID'

Answer 2

1)

select table_name
  from all_constraints
 where r_constraint_name = [your pk/uk constraint on PROJECTS(id)]

2)

select table_name
  from all_tab_columns
 where column_name = 'PROJECT_ID'

You may want to add an extra predicate containing the OWNER column.

Regards, Rob.