On Fri, Mar 25, 2016 at 7:07 PM, liviuslivius liviusliv...@poczta.onet.pl
[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 rdsc1...@gmail.com [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
_____________________________
