ODP: [firebird-support] Listing Table of Database in Order of Dependency

[liviuslivius [e-mail] [firebird-support]]

hi,
why you need this?i ask because it smells me wrong direction of doing something.
regards,Karol Bieniaszewski


-------- Oryginalna wiadomość --------
Od: "LtColRDSChauhan ***@gmail.com [firebird-support]" <firebird-***@yahoogroups.com>
Data: 25.03.2016 11:36 (GMT+01:00)
Do: firebird-***@yahoogroups.com
Temat: [firebird-support] Listing Table of Database in Order of Dependency


 









Hi,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)....Thanks and Regards,Rajiv

[LtColRDSChauhan [e-mail] [firebird-support]]

Post by liviuslivius ***@poczta.onet.pl [firebird-support]
hi,
why you need this?

I have been updating a deployed application of mine for last 8 yrs. The
database has changed from some 30 tables to 80 tables. To migrate data (i
have a .NET application of mine) i need to transfer data of tables from
least dependent onwards.

I use the following two stored procedures

SET TERM ^ ;
ALTER PROCEDURE DB_TBLS_ROWS_FK_0
RETURNS (
TBL_NAME varchar(50),
FKS smallint )
AS
BEGIN
for SELECT DISTINCT RDB$RELATION_NAME FROM RDB$RELATION_FIELDS WHERE
RDB$SYSTEM_FLAG=0 into :TBL_NAME
do begin
SELECT count(RDB$INDEX_NAME) FROM RDB$INDICES WHERE
RDB$RELATION_NAME= :TBL_NAME AND (RDB$FOREIGN_KEY IS NOT NULL) into :FKS;
suspend;
end
END^
SET TERM ; ^

GRANT EXECUTE
ON PROCEDURE DB_TBLS_ROWS_FK_0 TO SYSDBA;


SET TERM ^ ;
ALTER PROCEDURE DB_TBLS_ROWS_FK (
INCL_FKS smallint DEFAULT 1 )
RETURNS (
TBL_NAME varchar(50),
FKS smallint,
PK varchar(50),
TBL_FIELDS bigint,
TBL_ROWS bigint )
AS
declare variable STMT_TO_EXEC varchar(80);
BEGIN
for SELECT p.TBL_NAME, p.FKS FROM DB_TBLS_ROWS_FK_0 p order by p.FKS
into :TBL_NAME, :FKS
do begin
TBL_ROWS = null;
TBL_FIELDS = null;
STMT_TO_EXEC = 'select count(*) as nr_of_rows from ' || :TBL_NAME;
execute statement STMT_TO_EXEC INTO :TBL_ROWS;
select count(*) from rdb$relation_fields flds where
flds.RDB$RELATION_NAME = :TBL_NAME into :TBL_FIELDS;
suspend;

if(0 <> :INCL_FKS) then begin
for SELECT r.RDB$FOREIGN_KEY FROM RDB$INDICES r WHERE
r.RDB$RELATION_NAME= :TBL_NAME AND (r.RDB$FOREIGN_KEY IS NOT NULL) into :PK
do begin
/*TBL_NAME = null;*/
FKS = null;
suspend;
end
PK = null;
end
end
END^
SET TERM ; ^

GRANT EXECUTE
ON PROCEDURE DB_TBLS_ROWS_FK TO SYSDBA;


i ask because it smells me wrong direction of doing something.

Post by liviuslivius ***@poczta.onet.pl [firebird-support]
regards,
Karol Bieniaszewski
-------- Oryginalna wiadomość --------
Data: 25.03.2016 11:36 (GMT+01:00)
Temat: [firebird-support] Listing Table of Database in Order of Dependency
Hi,
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)
....
Thanks and Regards,
Rajiv