Rajiv wrote:
How can i list tables of a database in order of dependency i.e.:
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) ....
Set answers:
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.