Firebird: Listar auto-relacionamento através de Foreign Keys

1

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

Listando as tabelas e campos de auto-relacionamento através das FKs (Foreign Keys).

-- BUSCA AUTO-RELACIONAMENTO A --> 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
      IF ((:V_MASTERTABLE IS NOT NULL) AND (:V_TABLE = :V_MASTERTABLE)) 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

Comentários (1)

SELECT * FROM comments ORDER BY RAND() LIMIT 0, 100

Escreva um comentário

Security Code: