On Fri, Mar 25, 2016 at 7:07 PM, liviuslivius liviuslivius@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

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.

regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------
Od: "LtColRDSChauhan rdsc1964@... [firebird-support]" <firebird-support@yahoogroups.com>
Data: 25.03.2016 11:36 (GMT+01:00)
Do: firebird-support@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

--

  Regards,

  Lt Col (Retd) Rajiv D.S. Chauhan

  in.linkedin.com/in/ltcolrdschauhan

_____________________________