How to find all records on all tables that violate constraints?

Kjell Rilbe wrote:

It seems I've been a bit sloppy when introducing new constraints in my database. At least I found one table with a record that has null in a column that I've altered to not null.

So, is there any nice convenient way to find all such records in all tables? I'm thinking an execute statement that iterates system tables to find all not null columns and select where XXX is null on each such table and column...? I think that would suffice in this case, considering the set of constraints I have.

Svein Erling Tysvær answers:

I would expect this statement

SELECT list('SELECT ''' || TRIM(RDB$RELATION_NAME) ||
     ''' RELATION_NAME, ''' || TRIM(RDB$FIELD_NAME) ||
     ''' FIELD_NAME, COUNT(*) FROM ' || TRIM(RDB$RELATION_NAME) ||
     ' WHERE ' || TRIM(RDB$FIELD_NAME) || '||'''' IS NULL GROUP BY 1, 2 ', '
UNION ')
FROM RDB$RELATION_FIELDS
WHERE RDB$NULL_FLAG = 1

to return an SQL statement that you can execute to find which combination of tables and fields contains NULL values (though it will take time since it has to avoid using indexes, at least I don't think you'll find NULLs if you use indexes).

Kjell Rilbe adds:

Nice one! Thanks! After fixing some quotes (mixed case table names etc) it seems to work nicely. Got 910 "cases"... Will have to split them up I think - too many unions otherwise.

This is what I ended up with, issuing one select per table to reduce table scans:

select 'select ''' || trim(RDB$RELATION_NAME) || ''' "Table", count(*)
"Count" from "' || trim(RDB$RELATION_NAME) ||
'" where ' || list('"' || trim(RDB$FIELD_NAME) || '" || '''' is null', '
or ') || ';'
from RDB$RELATION_FIELDS
where RDB$NULL_FLAG = 1
group by trim(RDB$RELATION_NAME);

With over 200 tables I manually grouped them in groups of 25 with union and an outer select to only return rows with count <> 0.