>Hi, Hi Rajiv!
>How can i list tables of a database in order of dependency ie > >TABLE_1 (no foreign key references) >TABLE_2 (foreign key references, if any, in TABLE_1 ) >TABLE_3 (foreign key references, if any, in TABLE_1 / TABLE_2 ) >TABLE_4 (foreign key references, if any, in TABLE_1 / TABLE_2 / TABLE_3) Ideally, this would be how you designed your database, i.e. have a forced order of your tables and refuse foreign keys pointing to any tables further down the list. I'm not certain whether or not things will want to line up as nicely as you want them in an existing database. Nevertheless, I suggest you create a new table: CREATE TABLE TABLES_ORDERED ( ID INTEGER NOT NULL, RDB$RELATION_NAME CHAR( 31 ), CONSTRAINT PK_TABLES_ORDERED PRIMARY KEY ( ID ) ); CREATE GENERATOR TABLES_ORDERED_GEN; SET TERM ^^ ; CREATE TRIGGER TABLES_ORDERED_ID FOR TABLES_ORDERED ACTIVE BEFORE INSERT POSITION 0 AS begin if ( ( new.ID is null ) or ( new.ID = 0 ) ) then new.ID = gen_id( TABLES_ORDERED_GEN, 1 ); end ^^ SET TERM ; ^^ commit; fill it with those tables that have no foreign key (start by running DELETE FROM TABLES_ORDERED if the table is not empty): INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME) SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r WHERE RDB$SYSTEM_FLAG=0 AND NOT EXISTS(SELECT * FROM RDB$INDICES i WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME AND i.RDB$FOREIGN_KEY IS NOT NULL); and then, repeatedly, try: INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME) SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r WHERE RDB$SYSTEM_FLAG=0 AND NOT EXISTS(SELECT * FROM TABLES_ORDERED o /*Ignore tables already inserted*/ WHERE r.RDB$RELATION_NAME = o.RDB$RELATION_NAME) AND NOT EXISTS(SELECT * FROM RDB$INDICES i /*Only insert tables whose foreign key tables are inserted already*/ JOIN RDB$INDICES i2 ON i.RDB$FOREIGN_KEY = i2.RDB$INDEX_NAME AND r.RDB$RELATION_NAME <> i2.RDB$RELATION_NAME /*Omit this line if you don't want to include tables pointing to themselves*/ LEFT JOIN TABLES_ORDERED o ON i2.RDB$RELATION_NAME = o.RDB$RELATION_NAME WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME AND i.RDB$FOREIGN_KEY IS NOT NULL AND o.ID IS NULL); When you've done this enough times for no further table to be inserted, you can get your ordered list by simply running: SELECT RDB$RELATION_NAME FROM TABLES_ORDERED ORDER BY ID If you find that the list lacks two or more tables, investigate those remaining tables - maybe you have some circular dependencies? Note that I've never tried doing anything similar myself (I don't even think I've written a query that uses the RDB$FOREIGN_KEY before), and that there may well be errors in what I've written above. HTH, Set