I am saddled with an ERP database which lacks any foreign keys and therefore lacks referential integrity.
I am writing a script to check most of the major tables in our database for ophaned records.
For example, in this case I'm working with our Sales tables.
SLCDPM - Customer Master Table
SOMAST - Sales Order Master
SOITEM - Sales Order Items
SORELS - Sales Order Releases
Basically, for these (and a whole bunch of other tables) I need to check to see if there are records in the SORELS that don't appear in any table above it. Then take SOITEM and check above it. Etc.
I started writing scripts, but the number of lines gets kind of ridiculous. Here is where I started with just these 4 tables.
select 'Sales Order Master',* from somast where fcustno not in (select fcustno from slcdpm where ftype <> 'P')
SELECT 'Sales Order Item',* FROM soitem WHERE fsono NOT IN (SELECT fsono FROM somast)
select 'Sales Order Release',* from sorels where (fsono+finumber) not in (select (fsono+finumber) from SOITEM)
The reason I stopped was that I just realized that SORELS (the bottom table) only checks the table before it, not all of the tables before it.
Anyone know of a script I can use to make this more automated or a better way to do it?