#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Cape Town, South Africa
    Posts
    51
    Rep Power
    11

    Cool FireBird System Table Relations \ Metadata


    Hi

    I am trying to retreive the structure of a database via the firebird system tables. I don't want the ddl statements.

    Is there anywhere where i can find what the relationships are between the system tables? Like a ERD or something?

    I am having difficulties when trying to retreive the field types of a table as i can't seem to find the correct relationship between fields and types.

    Thanks Wayne
    Last edited by WayneB; May 17th, 2004 at 05:11 AM. Reason: Change wording
  2. #2
  3. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    Chapter 7 of the Language Reference manual lists all the systems tables and their fields. It is under the section called "System Tables and Views".

    I had never really looked at these tables very closely but I was able to throw together the following query that showed me the tables I had created and the columns in those tables. Is this the type of query you're looking for? If it is, make sure you test it before you use it. I just glanced at the results to make sure they were "correct" but didn't spend much time analyzing them.
    Code:
    SELECT a.RDB$RELATION_NAME, b.RDB$FIELD_NAME, b.RDB$FIELD_ID, d.RDB$TYPE_NAME,
           c.RDB$FIELD_LENGTH, c.RDB$FIELD_SCALE
    FROM   RDB$RELATIONS a
    INNER JOIN RDB$RELATION_FIELDS b
    ON     a.RDB$RELATION_NAME = b.RDB$RELATION_NAME
    INNER JOIN RDB$FIELDS c
    ON     b.RDB$FIELD_SOURCE = c.RDB$FIELD_NAME
    INNER JOIN RDB$TYPES d
    ON     c.RDB$FIELD_TYPE = d.RDB$TYPE
    WHERE  a.RDB$SYSTEM_FLAG = 0
      AND  d.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
    ORDER BY a.RDB$RELATION_NAME, b.RDB$FIELD_ID
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Location
    Cape Town, South Africa
    Posts
    51
    Rep Power
    11
    Yeah thanks.

    This is the kind of thing that i was looking for. The linking of fields to field types and Sprocs to Data types is were my problems was.

    I did not know that i had to hard code the Field type as string in the sql.

    I found the SQL that i was looking for by using a SQL Monitor to monitor the SQL generated by the ERD tools.
    Last edited by WayneB; May 19th, 2004 at 08:44 AM. Reason: Added more info

IMN logo majestic logo threadwatch logo seochat tools logo