Subject Re: [firebird-support] Find all records on all tables that violate constraints?
Author Dmitry Kuzmenko
Hello, Kjell!

Friday, February 28, 2014, 3:29:34 AM, you wrote:

KR> Hi,

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

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

I'm sorry, maybe you will not like my answer, but when I wanted to
find not null columns that have nulls, I wrote simple application,
with cycle for each table executing query searching for nulls in not
null columns (where field is null) by simple scanning records
with non-buffering query component (I did it with IBX.TIBSQL).

I know that it work long, but anyway, the task is to find record with
null that must not have null, so, scanning all records for the whole
database is necessary measure.

Thus, if you expert in execute statement - write it. Otherwise write
plain program with serveral simple for/while cycles.

My old app is about 70 lines in Delphi, and it is too big to post it
here. If you want me to be ashamed for the old crappy code, send me private message, I will
give you link to exe and pas sources. :-)

Dmitry Kuzmenko,