We had a weird requirement on our project recently..
Find all the Rows in All the tables that do not comply with the Constraints that we have in development but not in QA environments
Best way to do this we thought was to write a SQL statement against the table for each column that was going to have a Foreign Key constrained column and find out what data was not right or did not match the constraint. For example: If we have a INVOICE table that has a ITEMID on it. I want to find all the rows in the INVOICE table that have a ITEMID that does not exist in the ITEM table. Writing this SQL for our 400+ tables database was going to be huge task.
Oracles (or for that matter any databases metadata) metadata to the rescue and we ended up writing a SQL that would generate our above SQL.
here is the SQL that generated the above SQL
SELECT 'SELECT '''||table_name||'-'||column_name||''', count(*) FROM '|| table_name|| ' WHERE not exists (select 1 from '|| remote_table ||' where '|| remote_table||'.'||remote_column||' = '||table_name||'.'||column_name||') AND ' ||table_name||'.'||column_name||' IS NOT NULL UNION ALL' FROM ( SELECT a.table_name, column_name, ( SELECT table_name FROM user_constraints WHERE constraint_name = a.R_CONSTRAINT_NAME) remote_table, ( SELECT column_name FROM user_cons_columns WHERE constraint_name = a.R_CONSTRAINT_NAME) remote_column FROM user_constraints a, user_cons_columns b WHERE a.constraint_name = b.constraint_name AND a.constraint_type = 'R' )
This SQL generates SQL that when run will give us data about tables that do not match our constraints requirements. If you have a CUSTOMER table which has CUSTOMERTYPEID and STATUSID on it, then the SQL generated would be.
SELECT 'CUSTOMER-CUSTOMERTYPEID', COUNT(*) FROM CUSTOMER WHERE NOT EXISTS (SELECT 1 FROM CUSTOMERTYPE WHERE CUSTOMERTYPE.CUSTOMERTYPEID = CUSTOMER.CUSTOMERTYPEID) AND CUSTOMER.CUSTOMERTYPEID IS NOT NULL UNION ALL SELECT 'CUSTOMER-STATUSID', COUNT(*) FROM CUSTOMER WHERE NOT EXISTS (SELECT 1 FROM STATUS WHERE STATUS.STATUSID = CUSTOMER.STATUSID) AND CUSTOMER.STATUSID IS NOT NULL
Once the above SQL is run, the results will show us data that does not match the constraints we want to introduce into the QA environments.