Firebird list domains and data types

Question

I want to list all domains, their datatypes, and size.

Background

I've managed to do the query, based on this SO answer.

The basic code takes all fields:

SELECT
  *
FROM
  rdb$fields

I found that I could get fields from rdb$fields:

1. filter fields from this request by RDB$FIELD_NAME
2. get field type code from RDB$FIELD_TYPE
3. get field length from RDB$FIELD_LENGTH

Reference:

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx04-fields.html

---

Question

How to combine all this to list all domains, their datatypes, and size?

I want to get only domains created by users, not automatic ones.

Answer 1

The code:

select
    t.RDB$FIELD_NAME                     Name,
    case t.RDB$FIELD_TYPE
        when 7 then 'SMALLINT'
        when 8 then 'INTEGER'
        when 10 then 'FLOAT'
        when 12 then 'DATE'
        when 13 then 'TIME'
        when 14 then 'CHAR'
        when 16 then 'BIGINT'
        when 27 then 'DOUBLE PRECISION'
        when 35 then 'TIMESTAMP'
        when 37 then 'VARCHAR'
        when 261 then 'BLOB'
    end                                  Type_Name,
    t.RDB$CHARACTER_LENGTH               Chr_Length


from RDB$FIELDS t

     where coalesce( rdb$system_flag, 0) = 0 
       and not ( rdb$field_name starting with 'RDB$')

Also interesting, I could not find a system table with datatypes. Had to hardcode them from the reference.

Thanks for the help in comments:

@MarkRotteveel

RDB$TYPE contains types, but names them differently:

You can find all data types in the RDB$TYPE for RDB$FIELD_NAME = 'RDB$FIELD_TYPE' (although you will need to map some types as it lists SMALLINT as SHORT, INTEGER as LONG, BIGINT as INT64 and VARCHAR as VARYING)

Need to use field RDB$CHARACTER_LENGTH instead of RDB$FIELD_LENGTH.

Note that RDB$FIELD_LENGTH is the wrong column for char/varchar columns as it is the length in bytes (which depends on the character set), you need to use RDB$CHARACTER_LENGTH for the length in characters, and for numerical fields, you'll more likely need RDB$FIELD_PRECISION (+ RDB$FIELD_SCALE), you are also ignoring sub type information.

I needed the length of varchars only but appears RDB$FIELD_LENGTH = RDB$CHARACTER_LENGTH, 1 byte = 1 char for 1 byte character set.

If you use a 1 byte character set [1 byte = 1 char], but for example, UTF-8 is (max) 4 byte per character, so then the field_length = 4 x character_length

@Arioch

The most reliable way to get user domains:

To an extent one may use select * from rdb$fields where coalesce( rdb$system_flag, 0) = 0 and not ( rdb$field_name starting with 'RDB$') however no one prohibits user from manually/explicitly creating column named "RDB$1234567".