Home  ==>  papers  ==>  db  ==>  interbase  ==>  using_interbase_system_tables   

Using InterBase System Tables - Felix John COLIBRI.

  • abstract : The InterBase / FireBird System Tables: description of the main Tables, with their relationship and examples about how to extract information from them
  • key words : InterBase - FireBird - System Table
  • software used : Windows XP, InterBase 6.5
  • hardware used : Pentium 2.800Mhz, 512 M memory, 140 G hard disc
  • scope : InterBase 6 to 7.5, FireBird 1 to 2
  • level : Delphi developer - InterBase / FireBird developer
  • plan :

1 - InterBase / Firebird System Tables

InterBase, like all other Sql Engines, uses system tables to store the database schema and otherwise manage the complete engine.

Those tables are described in the InterBase Language Reference Manual, in a very alphabetic, "reference manual" style. Which means that it is difficult to relate the tables to each other, and, more important, to see how this knowledge could be used for our own development purposes.

In this article, we are going to present a more conceptual view of those tables, and show some request which extract different schema informations pieces from those Tables.

We have used the EMPLOYEE.GDB demo database to extract the examples presented in this paper.

2 - InterBase System Tables Description

2.1 - Conceptual architecture

Before looking at the detailed system tables, we can guess what should be involved:
  • any relational database consists of Tables, which are lists of rows, each row containing the same number of fields.
  • each field has a name, a type, some parameters, and can be specified explicitely as a Domain or implicitely as the column of a Table.
  • the Table can have additional elements: indexes, constraints, triggers
  • some other concepts, not tightly related to the Table / field cluster, will include generators, stored procedures, exceptions, roles, user defined functions
  • and there are some database wide tables (the database name, transactions, log_files ...)
Using those general ideas, and looking at the _id suffix or the references from one table to another in the system table definitions, we could build the following general schema (rdb$ removed, not all links displayed, not all System Tables):


2.2 - Column specifications

To create a Table, we have to describe each column (name, type, parameters). This can be done
  • either by directly specifying the Columns:

    CREATE TABLE country 
      (country_name VARCHAR(15), currency VARCHAR(10));

  • or by creating an explicit Domain, and using the Domain name when we create the Table

    CREATE DOMAIN projno AS CHAR(5);

    CREATE TABLE employee_project
      (emp_no empnoproj_id projno);

The information about Domains are kept in the rdb$fields Table, whereas the columns directly defined in a CREATE TABLE request are in the rdb$relation_fields Table.

The details of the collation, character set and array dimensions are kept in 3 separate tables.

The Tables defining the column types are linked in the following way (rdb$ removed)


The main information in the rdb$fields Table are (partial, reordered):



  • rdb$name is set during the Domain creation ("empno" from CREATE DOMAIN empno) or generated for Table columns created directly ("rdb$1" for the currency field of the country table)
  • rdb$system_flag is equal to 1 for System Tables
  • rdb$description: an optional description
  • the type is specified by those fields
    • rdb$field_type and rdb$field_sub_type specify the data type
    • rdb$field_length, rdb$field_scale and rdb$field_precision, rdb$character_length are size parameters
    • rdb$dimensions is used for ARRAY fields. This will also involve the rdb$dimension Table (see below)
    • rdb$collation_id and rdb$character_set_id are linked to the character tables (see below)
    Here is a summary of those fields:


  • the Domain can also have some constraints
    • rdb$null_flag. For instance:

      CREATE DOMAIN prodtype 
        AS VARCHAR(12) 
        NOT NULL

    • rdb$default_value and rdb$default_source specify default values:

      CREATE DOMAIN budget 
        AS NUMERIC(15, 2)
        DEFAULT 50000

    • rdb$validation_blr and rdb$validation_source specify constraints. Here are a couple of examples:

      CREATE DOMAIN budget 
        AS NUMERIC(15, 2)
        CHECK (VALUE > 10000 AND VALUE <= 2000000);
      CREATE DOMAIN custno 
        AS INTEGER
        CHECK (VALUE > 1000);
      CREATE DOMAIN deptno AS CHAR(3)
          (VALUE = '000
            OR (VALUE > '0AND VALUE <= '999') 
            OR VALUE IS NULL);
      CREATE DOMAIN ponumber AS CHAR(8)
      CREATE DOMAIN prodtype AS VARCHAR(12)
        CHECK (VALUE IN ('software', 'hardware', 'other')) NOT NULL;
      CREATE DOMAIN projno AS CHAR(5)

    • rdb$computed_blr and rdb$computed_source specify calculated fields:

      CREATE TABLE salary_history 
          emp_no empno,
          old_salary salary,
          percent_change DOUBLE PRECISION DEFAULT 0,
            COMPUTED BY 
              (old_salary + old_salary * percent_change / 100)

Here are the main columns of rdb$character_set:


and the rdb$collation Table


and the ARRAY dimensions are specified by:


To get the information about the domains, we can use the following request:

  FROM rdb$fields
  ORDER BY rdb$field_name


  • we can filter out the internal fields by testing the system flag as well as any number after the $ of the name
  • type and sub-type are decoded using the description above
  • we can display the different size parameters, and we can also get the character code and collation order
  • rdb$null_flag can be used to display NULL
  • rdb$default_value and rdb$default_source will display any default
  • rdb$validation_blr and rdb$validation_source will display the checks
Here is an example of the EMPLOYEE domains using our ColIbExp explorer:


2.3 - The Table Columns

As explained above, we can directly specify the Column attributes in the CREATE TABLE request. In this case
  • the information is stored in the rdb$relation_fields Table
  • an entry is generated in the rdb$fields Table, with an internally generated rdb$field_name
On the other hand, when we specify a Column using a previously defined Domain, the rdb$relation_fields simply references the rdb$fields Table. In addition, we may add additional constraints (NOT NULL, defaults), which will override the Domain parameters. Here is an example:

  AS CHAR(3)
    (VALUE = '000
       OR (VALUE > '0AND VALUE <= '999') 
  AS NUMERIC(15, 2)
  DEFAULT 50000
  CHECK (VALUE > 10000 AND VALUE <= 2000000);
CREATE DOMAIN phonenumber 

CREATE TABLE department 
    dept_no deptno NOT NULL,
    department VARCHAR(25) NOT NULL,
    head_dept deptno,
    mngr_no empno,
    budget budget,
    location VARCHAR(15),
    phone_no phonenumber  DEFAULT '555-1234'


  • dept_no adds NOT NULL to the deptno Domain
  • phone_no adds a DEFAULT value to phonenumber

Here are the relations between those tables:


The rdb$relation_fields Table main fields are:


Note that

  • all the size parameters are missing from this Table. So those informations come from the rdb$fields Table
  • there are no checks on the Columns, but we can add them at the Table level

If we want to get the complete definition of the columns of a Table, we can use the following request:

SELECT rdb$field_namerel_field.rdb$field_name,
  FROM rdb$relations rel
    JOIN rdb$relation_fields rel_field
      ON rel_field.rdb$relation_name = rel.rdb$relation_name
    JOIN rdb$fields field
      ON rel_field.rdb$field_source = field.rdb$field_name
  WHERE rel.rdb$relation_name = : RelationName
  ORDER BY rel_field.rdb$field_positionrel_field.rdb$field_name

Using this kind of request, here is a snapshot of the columns of the DEPARTMENT Table from the EMPLOYEE database:


2.4 - Tables

Tables are managed by the rdb$relations System Table. Tables are linked to many other database objects:
  • the Columns
  • the Views
  • the Indexes
  • the constraints and triggers
  • some security checks
The following schema displays the direct links to the rdb$relations Table:


The main fields of the rdb$relation Table are:



  • rdb$view_blr is NULL for Views
  • rdb$owner_name tells who created the Table, and is used to manage Table access

To get the list of all non system Table names, you may use:

SELECT rdb$relation_name
  FROM rdb$relations
          (rdb$system_flag = 0) 
          (rdb$system_flag IS NULL)
      (rdb$view_source IS NULL)
  ORDER BY rdb$relation_name

Note that

  • we have to test rdb$system_flag both for 1 and IS NULL, since InterBase does not always use the same criterion
  • fetching the list of Tables is so frequent that this request has been incorporated in our standard U_IBX.PAS helper UNIT.

More interestingly, to get the columns of a Table:

SELECT rdb$field_namerel_fld.rdb$field_name
  FROM rdb$relations rel
    JOIN rdb$relation_fields rel_fld
      ON rel_fld.rdb$relation_name = rel.rdb$relation_name
    JOIN rdb$fields fld
      ON rel_fld.rdb$field_source = fld.rdb$field_name 
  WHERE rel.rdb$relation_name = : RelationName 
  ORDER BY rel_fld.rdb$field_positionrel_fld.rdb$field_name

Note that

  • this request contains the raw information about the Table columns, but to display the presence of UNIQUE, PRIMARY KEY or FOREIGN KEY constraints on some column, we have to query additional System Tables, and this will be presented below.

2.4.1 - Indices

Table Indices are linked:
  • to the rdb$relations Table
  • to the rdb$segment Table where the columns used by the Index are defined
  • to the rdb$relation_constraint Table to handle the foreign key constraints
Here is a picture of those relationships:


Indices are created

  • by explicit CREATE INDEX requests. Here are a couple of definitions:

    CREATE INDEX custnamex 
      ON customer(customer);

    CREATE INDEX custregion 
      ON customer(countrycity);

      ON department(budget);

    CREATE UNIQUE INDEX prodtypex 
      ON project(productproj_name);

  • implicitely by the system to handle
    • UNIQUE constraints
    • PRIMARY KEY constraints
    • for FOREIGN KEY constraints
    • ORDER BY clauses
For instance, here is the script which creates the DEPARTMENT Table of the EMPLOYEE database:

CREATE TABLE department 
    dept_no deptno NOT NULL,
    department VARCHAR(25) NOT NULL,
    head_dept deptno,
    mngr_no empno,
    budget budget,
    location VARCHAR(15),
    phone_no phonenumber  DEFAULT '555-1234',

    UNIQUE (department) ,
    PRIMARY KEY (dept_no) ,
      REFERENCES department (dept_no) ,
      REFERENCES employee (emp_no)

  ON department(budget);

and here is, from the rdb$indices Table, the rows about the DEPARTMENT Table:



  • the first row is the UNIQUE index on DEPARTMENT (unique flag)
  • the second row is the PRIMARY KEY index (also unique)
  • third and fifth are about FOREIGN KEY indices (with the fk constraint reference)
  • the forth is our hand created index (with type=1 for descending)

The main columns of the rdb$indices Table are:


Note that

  • this table contains also a rdb$statistics field, which is used to optimize the query access strategy

The main columns of the rdb$index_segments Table are:


To extract the Indices from a Table, you may use:

SELECT ix.rdb$relation_nameix.rdb$index_name
    ix.rdb$unique_flag,  ix.rdb$index_type 
  FROM rdb$indices ix 
    JOIN rdb$relations rel 
      ON ix.rdb$relation_name = rel.rdb$relation_name 
      (rel.rdb$system_flag <> 1 OR rel.rdb$system_flag IS NULL
      rel.rdb$relation_name = : RelationName 
        (SELECT * 
           FROM rdb$relation_constraints rel_con 
           WHERE rel_con.rdb$index_name = ix.rdb$index_name
  ORDER BY ix.rdb$relation_nameix.rdb$index_name

and to get the columns of some index:

  FROM rdb$index_segments 
  WHERE rdb$index_name = : indexname 
  ORDER BY rdb$field_position

2.4.2 - Triggers

The rdb$triggers manages the Triggers related to the Tables. Triggers are created
  • explicitely. For instance, to get a new key, we can use a Generator, and an associated Trigger which fires before insert:

    CREATE TRIGGER set_cust_no 
      FOR customer 
          NEW.cust_no = gen_id(cust_no_gen, 1);

  • implicitely to handle check constraints. This will be examined below.

The main fields are:



  • rdb$trigger_type encodes the before/after event as well as the insert/update/delete cause

To get the user Triggers (those not generated to handle check constraints), we can use:

  FROM rdb$triggers trig 
    JOIN rdb$relations rel 
      ON trig.rdb$relation_name = rel.rdb$relation_name 
      rel.rdb$relation_name = : TableName 
      (rel.rdb$system_flag <> 1 OR rel.rdb$system_flag IS NULL
        (SELECT * 
           FROM rdb$check_constraints chk_con 
             trig.rdb$trigger_name = chk_con.rdb$trigger_name
  ORDER BY trig.rdb$relation_nametrig.rdb$trigger_type

2.4.3 - Constraints

We can impose 5 kind of constraints:

The constraints can be set a the Domain level, while creating a Table, or using specific constraint clauses or requests.

The constraints are managed using three new System Tables:

  • rdb$relation_constraints
  • rdb$check_constraints
  • rdb$ref_constraints
To understand how this happens, lets look at tw examples.

First, here are snapshots for the DEPARTMENT Table:

  • the rdb$relation_constraints :


  • here are the entries in the rdb$check_constraints Table


  • the rdb$ref_constraints:


  • and here are the Indices:


And the snapshot for the CUSTOMER Table which has a CHECK constraint:
  • the creation requests are:

      CHECK (VALUE > 1000);
    CREATE DOMAIN firstname AS VARCHAR(15);
    CREATE DOMAIN lastname AS VARCHAR(20);
    CREATE DOMAIN phonenumber AS VARCHAR(20);
    CREATE DOMAIN addressline AS VARCHAR(30);
    CREATE DOMAIN countryname AS VARCHAR(15);

    CREATE TABLE customer 
        cust_no custno NOT NULL,
        customer VARCHAR(25) NOT NULL,
        contact_first firstname,
        contact_last lastname,
        phone_no phonenumber,
        ADDRESS_LINE1 addressline,
        ADDRESS_LINE2 addressline,
        city VARCHAR(25),
        state_province VARCHAR(15),
        country countryname,
        postal_code VARCHAR(12),
        on_hold CHAR(1) DEFAULT NULL,

        PRIMARY KEY (cust_no)

    CREATE INDEX custnamex ON customer(customer);
    CREATE INDEX custregion ON customer(countrycity);

    ALTER TABLE customer 
      ADD FOREIGN KEY (countryREFERENCES country (country);
    ALTER TABLE customer ADD
      CHECK (on_hold IS NULL OR on_hold = '*');

  • the rdb$relation_constraints:


  • the rdb$check_constraints:


  • the rdb$ref_constraints:


  • the Indices:


  • the rdb$triggers:


Here is the relationship between those Tables:


Note that

  • for the "customer custno NOT NULL" column we find
    • in rdb$relations_constraints a row with "INTEG_57, NOT NULL" row
    • in rdb$check_constraints a "INTEG_57, CUST_NO" row. However the "CUST_NO" appears in the rdb$trigger_name, but there is no such trigger at all: this is the column name
  • for the "PRIMARY KEY (cust_no)" constraint we find
    • in rdb$relations_constraints a row with "INTEG_60, PRIMARY KEY, RDB$PRIMARY22" row
    • in rdb$indices a "RDB$PRIMARY22, unique" row
  • for the FOREIGN KEY constraint "FOREIGN KEY (country) REFERENCES country (country)" constraint
    • in rdb$relations_constraints a row with "INTEG_61, FOREIGN KEY, RDB$FOREIGN23" row
    • in rdb$ref_constraints an "INTEG_61, INTEG_2" row
    • in rdb$indices a "RDB$FOREIGN23, RDB$PRIMARY1" entry, with a row on the COUNTRY Table
  • for the CHECK constraint, "CHECK (on_hold IS NULL OR on_hold = '*')"
    • in rdb$relations_constraints a row with "INTEG_59, CHECK" row
    • in rdb$check_constraints two entries:
      • "INTEG_59, CHECK_9"
      • "INTEG_59, CHECK_10"
    • in the rdb$triggers table, two triggers, one "BEFORE UPDATE" and the second "BEFORE INSERT"
  • for the UNIQUE constraint (in the DEPARTMENT Table):
    • in rdb$relations_constraints a row with "INTEG_15, UNIQUE, RDB$4"
    • in the rdb$indices, a row "RDB4, unique" index

The 3 constraints tables main columns are:
  • for rdb$relations_constraints:


  • for the rdb$check_constraints:


  • for the rdb$ref_constraints:


Here are some examples of querying the system tables about the different constraints:
  • to find out if there are some PRIMARY KEY or UNIQUE constraint:

    SELECT * 
      FROM rdb$relation_constraints rel_k 
             rel_k.rdb$constraint_type = ''PRIMARY KEY'
              rel_k.rdb$constraint_type = ''UNIQUE'
          rel_k.rdb$relation_name = : relationname 

    and we have to remove the 'INTEG_nnn' rows from the result

  • to find the CHECK constraints on a Table:

    SELECT * 
      FROM rdb$triggers trg 
        JOIN rdb$check_constraints chk_k 
          ON trg.rdb$trigger_name = chk_k.rdb$trigger_name 
          trg.rdb$relation_name = : TableName 
          trg.rdb$trigger_type = 1 
           (SELECT rdb$constraint_name 
              FROM rdb$relation_constraints rel_k 
                chk_k.rdb$constraint_name = rel_k.rdb$constraint_name

2.5 - Views

Here is a simple View from the EMPLOYEE database

CREATE view phone_list
    SELECT emp_nofirst_namelast_namephone_ext,
      FROM employeedepartment
      WHERE employee.dept_no = department.dept_no

The schema of this View is specified in the rdb$relations System Table, with rdb$view_blr<> NULL. Columns are specified in rdb$relation_fields.

In order to list the Views, we can use:

SELECT rdb$relation_name
  FROM rdb$relations 
      (rdb$system_flag <> 1 OR rdb$system_flag IS NULL
      NOT rdb$view_blr IS NULL 
      rdb$flags = 1 

The rdb$view_relations contains information for aliasing View column names


2.6 - Stored Procedure

Stored Procedures are managed by the rdb$procedures System Table, which contains
  • the rdb$procedure_name
  • the rdb$procedure_source Blob contains the body of the Stored Procedure
Here are the main fields:


The input and output parameters are handled by the rdb$procedure_parameters. This Table simply links the rdb$procedures Table and the rdb$Fields Table, with an additional rdb$parameter_type column to indicate whether the parameter is an input or output parameter


The Tables involved are:


As an example, the creation script of the GET_EMP_PROJ procedure is:

      emp_no SMALLINT
      proj_id CHAR(5)
    FOR SELECT proj_id
      FROM employee_project
      WHERE emp_no = : emp_no
      INTO : proj_id

The parameter informations displayed by the ColIbExp utility are:


Here is a request which lists all Stored Procedure names:

SELECT rdb$procedure_name
  FROM rdb$procedures

and to get information about the parameters we can use:

SELECT rdb$parameter_namerdb$field_typerdb$field_sub_type
  FROM rdb$procedure_parameters sp_param
    JOIN rdb$fields fld 
      ON sp_param.rdb$field_source = fld.rdb$field_name 
    sp_param.rdb$procedure_name = : sp_name

2.7 - User Defined Functions (UDFs)

UDF are managed by the rdb$functions and rdb$function_arguments Table, in a similar fashion as the Stored Procedures

2.8 - Generators

The rdb$generators table manages the Generators. The main columns are:


Note that

  • the current value of the generator is not contained in this table, but managed directly by the System

2.9 - Security

The USERs are managed by the separate Server wide ISC4.GDB database.

For each database, the rdb$role and rdb$user_privilege are used to handle Roles and Grants.

To define Roles, we use rdb$roles:


Grants are handled by rdb$user_privileges:



  • rdb$grantor: the User who gave the Grant
  • rdb$user tells which User receives the Grant (User, Role, Procedure, Trigger, View)
  • rdb$relation_name tells which object was involved in the Grant (Table, View, Procedure, Role)
  • rdb$privilege : the kind of Grant

Here is a Grant request

  ON country 
  TO public 
  WITH GRANT option;

and here are the corresponding rdb$user_privileges Table rows:


3 - Comments

As usual:
  • please tell us at if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will be helpful for other readers
  • we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to
  • or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button
    Name :
    E-mail :
    Comments * :

  • and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your blog or newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.

4 - References

  • The System Tables are described in the InterBase Language Reference Manual, (LANGREF) Chapter 7 - System Tables and Views. This link is an IBPhoenix link for the InterBase 6 manual, but if this link does not work, Google will easily find other links for this manual.

5 - The author

Felix John COLIBRI works at the Pascal Institute. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi Xe_n migrations, refactoring), Delphi Consulting and Delph training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP  /  UML, Design Patterns, Unit Testing training sessions.
Created: feb-07. Last updated: dec-15 - 99 articles, 220 .ZIP sources, 1068 figures
Copyright © Felix J. Colibri 2004 - 2015. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute


+ Home
  + articles_with_sources
    + database
      + interbase
        – interbase_tutorial
        – stored_proc_grammar
        – using_system_tables
      – firebird_trans_simulator
      + sql_server
      + bdp
      – db_refactoring
      – sql_parser
      – sql_to_html
      – sniffing_interbase
      – eco_tutorial
      – dbx4_programming
      – blackfishsql
      – rave_pdf_intraweb
      – rave_reports_tutorial
      – rave_reports_video
      – embarcadero_er/studio
      + firedac
      – bde_unidac_migration
    + web_internet_sockets
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + firemonkey
    + compilers
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
Site Map
– search :

RSS feed