Subject [firebird-support] Re: How to list the tables and field wich use the primary key from one table as FK
Author Svein Erling Tysvær
>I think you are 100 % right , the problem is that i am not working in the developpement team but in customer
>support , I suspect some software update to cause the problem but I have no access on that , the only thing I can
>do is to try to repair the best way for my client and I can't only validate and mend , I have also to put back the
>records from a backup with new ID or the problem will not be solve completely .
>We have about 500 servers too, the structure is from 2 up to 20 clients , little workgroup under windows XP and 7
>I don't think the problem concern the firebird side , I know it's very strenght.

You also ought to try to influence your development team somehow. Normally, it would not be OK for you to have 100 corruptions (assuming an average of 2 per week) when Alan hasn't experienced any corruption at all. Hardware problems and faulty UDFs used to be ways to make Firebird fail.

The following may or may not help you:

select i.rdb$relation_name, iseg.rdb$field_name
from rdb$relation_constraints rec
join rdb$ref_constraints rfc on rec.rdb$constraint_name = rfc.rdb$constraint_name
join rdb$relation_constraints rec2 on rfc.rdb$const_name_uq = rec2.rdb$constraint_name
join rdb$index_segments iseg2 on rec2.rdb$index_name = iseg2.rdb$index_name
join rdb$indices i2 on rec2.rdb$index_name = i2.rdb$index_name
join rdb$indices i on rec.rdb$index_name = i.rdb$index_name
join rdb$index_segments iseg on rec.rdb$index_name = iseg.rdb$index_name
where i2.rdb$relation_name = :MyTableName
and iseg2.rdb$field_name = :MyPKFieldName

It is not tested, I know it does prepare and that it is possible to get some results, but I don't know if they are correct or complete (I'm assuming one field for each primary key).