> Friday, February 28, 2014, 3:29:34 AM, you wrote:
>
> KR> It seems I've been a bit sloppy when introducing new constraints
> KR> in my database. At least I found one table with a record that has
> KR> null in a column that I've altered to not null.
>
> KR> So, is there any nice convenient way to find all such records in
> KR> all tables?
>
> Well, I have an idea that can make huge speedup of the "whole database
> scanning process" - you should check only those tables, that have
> rdb$format > 1, because tables, that have rdb$format = 1 was not
> altered since last restore or their first creation.
>
>Thanks Dmitry! I think I can figure it out either way (execute statement or C#
>app). I was trying to save some time ny not reinventing the >wheel. :-)
Reinventing the wheel can sometimes be fun, Kjell. 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).
HTH,
Set
