Smart Oracle tool to find missing field relationships

Question

Does Oracle have a tool I can use to analyze a database and help determine possible missing field relationships? We have a legacy database with 150+ tables and many relationships are missing. We could go through it by hand but an automated tool might be useful. So find things like missing foreign keys and whatnot.

Answer 1

This might be a good start

select column_name, table_name, data_type
from user_tab_cols
order by column_name, table_name

Answer 2

I've had to do this a few times now. I find it's a very human-intelligence kind of thing - helped by running a lot of queries across both the data dictionary (e.g. EvilTeach's query), querying sample data from the columns, examining how the data is created by the application, and understanding the business requirements and user processes.

For example, in many legacy applications I find constraints (including referential integrity constraints) that are checked and implemented in the front-end application, which means the data follows the constraint (almost 100% :) ) but it's not actually constrained at the database level. Lots of fun results.

I'd be surprised if a tool could do any of this automatically and yield useful results.

Answer 3

You could find a POSSIBLE foreign key absence if you assume that you could identify a POSSIBLE foreign key relationshinp by finding columns with equal names and data types in different tables, one of which is a primary key, and another one has no reference to that key.

You could use a query like this:

select c1.TABLE_NAME, c1.COLUMN_NAME, c2.TABLE_NAME, c2.COLUMN_NAME
  from user_tab_columns c1,
       user_tables      at1,
       user_tab_columns c2,
       user_tables      at2
 where c1.COLUMN_NAME = c2.COLUMN_NAME
   and c1.DATA_TYPE = c2.DATA_TYPE
   and c1.TABLE_NAME = at1.TABLE_NAME
   and c2.TABLE_NAME = at2.TABLE_NAME
   and c1.TABLE_NAME != c2.TABLE_NAME
   /*and c1.TABLE_NAME = 'TABLE' --check this for one table
     and c1.COLUMN_NAME = 'TABLE_PK'*/
   and not exists (select 1
          from user_cons_columns ucc,
               user_constraints  uc,
               user_constraints  uc2,
               user_cons_columns ucc2
         where ucc.CONSTRAINT_NAME = uc.CONSTRAINT_NAME
           and uc.TABLE_NAME = ucc.TABLE_NAME
           and ucc.table_name = c1.TABLE_NAME
           and ucc.column_name = c1.COLUMN_NAME
           and uc.CONSTRAINT_TYPE = 'P'
           and uc2.table_name = c2.TABLE_NAME
           and ucc2.column_name = c2.COLUMN_NAME
           and uc2.table_name = ucc2.table_name
           and uc2.r_constraint_name = uc.constraint_name
           and uc2.constraint_type = 'R')

This one (a sketch, optimized in no way, though) scans through all pairs of column name-type equality, and finds if one is a PK, and another one doesn't reference it.

But, and here I agree with Jeffrey, it's a very human-intelligence kind of thing, and no tool will do this for sure. In any case you'll have to do it by hand.