Firebird System Tables (Part I)

Hi, whew it's has been long time since my last time post, yes I a little bit busy now, with some of my own project and some of my family work. Ok, now I want to share about Firebird System Tables, they are tables that side on every FB database that consist of metadata of user tables (table that you created), many question about this and also many purpose to know about this.

I often use this technique when I develop a database that consist of many tables and where there is an error on one of the field and then I must know this field is belong on which table, to solve that problem, I create a view like this:

CREATE VIEW "vSYS_FindField"(
"tableName",
"fieldName",
"default_val")
AS
select r.rdb$relation_name, f.rdb$field_name, f.rdb$default_source
from RDB$RELATIONS r, RDB$RELATION_FIELDS f
where r.rdb$system_flag = 0
and r.rdb$relation_name=f.rdb$relation_name;

The FB System Tables can be known from "RDB$RELATIONS.RDB$SYSTEM_FLAG" greater than 0 (zero). The "RDB$RELATIONS" is a system table for user table and user view (off course it also contain of many things that you can look by yourself ;P) And "RDB$RELATION_FIELDS" is as you may guess is a system table for user field. It's now up to you to improve that query, maybe you want to include some more information, then go ahead :)

NB: Thank's Romkin for the correction, now I already applied your correction in this short article :)