There is a table1 that has foreign key to table2 (one table2 to many table1). How to get list of table2 fields:
"table2.f1"
"table2.f2"
"table2.f3"
just by giving "table1.f3" (that has foreign key to table2). How to select this from system tables?
Lists all referenced by FK tables and fields.
SELECT
detail_index_segments.rdb$field_name AS field_name,
master_relation_constraints.rdb$relation_name AS reference_table,
master_index_segments.rdb$field_name AS fk_field
FROM
rdb$relation_constraints detail_relation_constraints
JOIN rdb$index_segments detail_index_segments ON detail_relation_constraints.rdb$index_name = detail_index_segments.rdb$index_name
JOIN rdb$ref_constraints ON detail_relation_constraints.rdb$constraint_name = rdb$ref_constraints.rdb$constraint_name -- Master indeksas
JOIN rdb$relation_constraints master_relation_constraints ON rdb$ref_constraints.rdb$const_name_uq = master_relation_constraints.rdb$constraint_name
JOIN rdb$index_segments master_index_segments ON master_relation_constraints.rdb$index_name = master_index_segments.rdb$index_name
WHERE
detail_relation_constraints.rdb$constraint_type = 'FOREIGN KEY'
AND detail_relation_constraints.rdb$relation_name = :table_name