|Subject||RE: [firebird-support] Find all records on all tables that violate constraints?|
|Author||Svein Erling Tysvær|
> Friday, February 28, 2014, 3:29:34 AM, you wrote:Reinventing the wheel can sometimes be fun, Kjell. I would expect this statement
> 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. :-)
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 ', '
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).