On Fri, Mar 25, 2016 at 7:07 PM, liviuslivius liviuslivius@poczta.onet.pl [firebird-support] <firebird-support@yahoogroups.com> wrote:
GRANT EXECUTE
ON PROCEDURE DB_TBLS_ROWS_FK_0 TO SYSDBA;
GRANT EXECUTE
ON PROCEDURE DB_TBLS_ROWS_FK TO SYSDBA;
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 ; ^
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
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;
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 ; ^
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@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
_____________________________
__._,_.___
Posted by: LtColRDSChauhan <rdsc1964@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Save time and get your email on the go with the Yahoo Mail App
Get the beautifully designed, lighting fast, and easy-to-use, Yahoo Mail app today. Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
.
__,_._,___
No comments:
Post a Comment