Foreign key integrity - is there a way to verify it programmatically?

Here's my situation - I'm working on a large scale data cleanup project and part of my duties include finding and fixing data anomolies such as date errors (someone entered 2070 for the year instead of 2007) or values that are out of range.

The other thing I'm on the lookout for is tables that *probably* should have a foreign key relationship but for whatever reason do not. Fortunately the data administration group here is pretty good as most of the tables do in fact have the correct keys in place. The other day however I stumbled onto a problem that is of great concern to me. In plain English, here's what I hope is an accurate and understable example of the problem:

I have an employee table with a numeric field called ee_id as it's primary key. The rest of the columns in the table are standard stuff that you'd normally have in a table like this. Throughout the rest of the database are dozens of other tables that contain an ee_id column and have a foreign key relationship established so that if someone tries to delete an employee but that ee_id exists in another table somewhere, the database will disallow it.

Strictly by accident I ran the following query on a table that does have a foreign key but still got rows returned anyway:

     select * from eblue where ee_id not in (select ee_id from employee);

Given that the foreign key is there, I shouldn't have gotten any rows back but unfortunately did. This brings me to the question I need an answer for - Is there any way of validating all of the foreign keys in the database to see if there are other anomolies like this?

Although I could write SQL script to do it, since there are over 500 tables I was hoping for an automated solution if one exists.

Thanks for reading,
Christopher Received on Thu Dec 20 2007 - 16:05:12 CST