Amazing world about data

Developer, ThoughtWorker, Data Architect and Data Specialist

Writing a SQL to generate a SQL

Generating SQL code using SQL code.

1 minute read

We had a weird requirement on our project recently..

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

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.

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.

Recent posts


See more