Firebird: Listando tabelas de relacionamento FK cruzado

0

Posted on : 02-07-2012 | By : Paulo H Oliveira | In : Banco de Dados, Firebird

No SQL abaixo lista os relacionamentos das tabelas de um Banco de Dados que contem relacionamento através das FKs (Foreign Key) cruzados. Ex.: A tabela CLIENTE tem uma FK com a tabela EMPRESA e a tabela EMPRESA tem uma FK para a tabela CLIENTE.

-- BUSCA RELACIONAMENTO CRUZADO A --> B --> A
EXECUTE BLOCK RETURNS(
R_TABLE        VARCHAR(300),
R_FOREIGN_KEY  VARCHAR(300),
R_FIELDFK      VARCHAR(300),
R_MASTERTABLE  VARCHAR(300),
R_FIELDPK      VARCHAR(300))
AS
DECLARE VARIABLE V_TABELA       VARCHAR(300);
DECLARE VARIABLE V_TABLE        VARCHAR(300);
DECLARE VARIABLE V_FOREIGN_KEY  VARCHAR(300);
DECLARE VARIABLE V_FIELDFK      VARCHAR(300);
DECLARE VARIABLE V_MASTERTABLE  VARCHAR(300);
DECLARE VARIABLE V_FIELDPK      VARCHAR(300);
DECLARE VARIABLE V_RETORNA      VARCHAR(2);
BEGIN
  FOR SELECT RDB$RELATION_NAME
        FROM RDB$RELATIONS
       WHERE NOT RDB$RELATION_NAME LIKE '%$%'
         AND RDB$VIEW_BLR IS NULL
        INTO :V_TABELA
  DO
  BEGIN
    FOR SELECT A.RDB$RELATION_NAME AS R_TABLE,
               A.RDB$CONSTRAINT_NAME AS R_FOREIGN_KEY,
               E.RDB$FIELD_NAME AS R_FIELDFK,
               C.RDB$RELATION_NAME AS R_MASTERTABLE,
               D.RDB$FIELD_NAME AS R_FIELDPK
          FROM RDB$REF_CONSTRAINTS B, RDB$RELATION_CONSTRAINTS A, RDB$RELATION_CONSTRAINTS C,
               RDB$INDEX_SEGMENTS D, RDB$INDEX_SEGMENTS E, RDB$INDICES I
         WHERE (A.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY')
           AND (A.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME)
           AND (B.RDB$CONST_NAME_UQ=C.RDB$CONSTRAINT_NAME)
           AND (C.RDB$INDEX_NAME=D.RDB$INDEX_NAME)
           AND (A.RDB$INDEX_NAME=E.RDB$INDEX_NAME)
           AND (A.RDB$INDEX_NAME=I.RDB$INDEX_NAME)
           AND (A.RDB$RELATION_NAME = :V_TABELA)
         ORDER BY A.RDB$RELATION_NAME, A.RDB$CONSTRAINT_NAME, D.RDB$FIELD_POSITION, E.RDB$FIELD_POSITION
          INTO :V_TABLE,
               :V_FOREIGN_KEY,
               :V_FIELDFK,
               :V_MASTERTABLE,
               :V_FIELDPK
    DO
    BEGIN
      V_RETORNA = '';
      SELECT FIRST 1
             'OK'
        FROM RDB$REF_CONSTRAINTS B, RDB$RELATION_CONSTRAINTS A, RDB$RELATION_CONSTRAINTS C,
             RDB$INDEX_SEGMENTS D, RDB$INDEX_SEGMENTS E, RDB$INDICES I
       WHERE (A.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY')
         AND (A.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME)
         AND (B.RDB$CONST_NAME_UQ=C.RDB$CONSTRAINT_NAME)
         AND (C.RDB$INDEX_NAME=D.RDB$INDEX_NAME)
         AND (A.RDB$INDEX_NAME=E.RDB$INDEX_NAME)
         AND (A.RDB$INDEX_NAME=I.RDB$INDEX_NAME)
         AND (A.RDB$RELATION_NAME = :V_MASTERTABLE)
         AND (C.RDB$RELATION_NAME = :V_TABLE)
        INTO :V_RETORNA;
      IF (:V_RETORNA IS NULL) THEN
        V_RETORNA = '';
      IF (:V_RETORNA = 'OK') THEN
      BEGIN
        R_TABLE       = :V_TABLE;
        R_FOREIGN_KEY = :V_FOREIGN_KEY;
        R_FIELDFK     = :V_FIELDFK;
        R_MASTERTABLE = :V_MASTERTABLE;
        R_FIELDPK     = :V_FIELDPK;
        SUSPEND;
      END
    END
  END
END

Autor: Robson Zambroti

Compartilhe :

  • Stumble upon
  • twitter

Escreva um comentário

Security Code: