>>I am looking for any utilities to automate the process of looking for child records that have missing parent key records.
>>
>>For instance Job_Line_Item records, that somehow do not have the matching parent key record.
>>
>>Job_Line_Item.job_num = Job.job_num
>>
>>Believe it or not, I am ashamed to say that I have found cases where I have Job_Line_Item records that do not have the associated parent record present.
>>
>>So now I want to check all the Parent-Child schemas that I have, which are many.
>>
>>All my data is presently is in free tables. I need to clean it up so I can push it up to SQL Server.
>>
>>So, are there any tools to automate this, or is it just a manual process?
>>
>>I figure I will want this to be a weekly process, just to make sure I have good data structure in place. That is why I want to have it somewhat automated.
>>
>>It will also help me discover where this is happening so I can fix my programs to prevent it from happening.
>
>Well, first of all, you should implement relational integrity, to avoid such things in the first place. Of course, it might require re-writing some of your existing code. Specifically, your current code might delete the parent record first and then the child record. Your new code will have to delete child records first (if you choose to "Restrict"), or ONLY delete the child record (in cases where you feel comfortable with "Cascade").
>
>To check for the missing parent records, you can either use a subquery:
>
>

>select * from Child where KeyField not in (select KeyField from Parent)
>

>
>or use an outer join:
>
>

>select child.*;
>  from Parent right join Child on Parent.KeyField = Child.KeyField;
>  where isnull(Parent.SomeField)
>

>
>... since the non-found records from the parent will have null values.

To make it SQL Server compatible use this syntax

>select child.*;
>  from Parent right join Child on Parent.KeyField = Child.KeyField;
>  where Parent.SomeField IS NULL