I need to display the name of all tables that depend on each other. Such tables 200 + dependent. For example, table X has FK from Y and Y has FK from Z. That is, search by FK. Can I implement FK search from the list of tables, as well as from the found tables?

Here is a query that searches for names for a single table (M_KONTRAH_GL):

SELECT master_relation_constraints.rdb$relation_name AS reference_table 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 JOIN rdb$relation_constraints master_relation_constraints ON rdb$ref_constraints.rdb$const_name_uq = master_relation_constraints.rdb$constraint_name WHERE detail_relation_constraints.rdb$constraint_type = 'FOREIGN KEY' AND detail_relation_constraints.rdb$relation_name = 'M_KONTRAH_GL'
  • So what is the question, what exactly did you fail? - German Borisov
  • Herman Borisov, the question is how to do a search in several tables, as well as to the found tables to the first. - YoFi
  • 2
    Well, probably remove the filter by the table name, but add this name to the list of fields of the result set ... - Akina
  • Remove AND detail_relation_constraints.rdb$relation_name = 'M_KONTRAH_GL' and get all the tables - Sergey
  • The database has 2k tables, you need to specify several tables for which to search. - YoFi

1 answer 1

 SELECT detail_relation_constraints.rdb$relation_name AS base_table, master_relation_constraints.rdb$relation_name AS reference_table 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 JOIN rdb$relation_constraints master_relation_constraints ON rdb$ref_constraints.rdb$const_name_uq = master_relation_constraints.rdb$constraint_name WHERE detail_relation_constraints.rdb$constraint_type = 'FOREIGN KEY' AND detail_relation_constraints.rdb$relation_name IN ('table1','table2', .. ,'tableN') ORDER BY 1,2
  • Thank you so much! - YoFi
  • As I understand it, the FK search is implemented here only for the tables listed in the list. Is it possible to implement the FK search from the list of tables, as well as from the tables found? - YoFi
  • Firebird 2.5 supports recursive CTE. So no contraindications. - Akina
  • can you please help write recursion? - YoFi
  • Me not. I have not approached him for 8 years now ... and it is lazy to study anew. But it would be better if you ask this question as a new one. - Akina