InterBase/FireBird – querying the system tables to get your actually used field/column types
Posted by jpluimers on 2009/08/13
Shortly, I’ll post a blog entry about find out which columns are not based on domains.
In order get the right definition for those domains, I needed a way to get that info from the DB, preferably in a SQL-statement (that saves you starting your application development environment).
The SQL-statement is based on my initial browsing of these documents:
- InterBase 2009 language reference (available from the Embarcadero documentation site)
- Firebird 2.1 language reference (available from the Firedbird documentation index)
- some educated guesswork :-)
If you find errors or omissions, please let me know.
So here it is:
select
f.rdb$field_type, t.rdb$type_name, f.rdb$field_sub_type,
f.rdb$character_length, f.rdb$field_scale,
f.rdb$field_length,
st.rdb$type_name as rdb$sub_type_name,
case f.rdb$field_type
when 7 then 'smallint'
when 8 then 'integer'
when 16 then 'int64'
when 9 then 'quad'
when 10 then 'float'
when 11 then 'd_float'
when 17 then 'boolean'
when 27 then 'double'
when 12 then 'date'
when 13 then 'time'
when 35 then 'timestamp'
when 261 then 'blob'
when 37 then 'varchar'
when 14 then 'char'
when 40 then 'cstring'
when 45 then 'blob_id'
end as "ActualType",
case f.rdb$field_type
when 7 then
case f.rdb$field_sub_type
when 1 then 'numeric'
when 2 then 'decimal'
end
when 8 then
case f.rdb$field_sub_type
when 1 then 'numeric'
when 2 then 'decimal'
end
when 16 then
case f.rdb$field_sub_type
when 1 then 'numeric'
when 2 then 'decimal'
else 'bigint'
end
when 14 then
case f.rdb$field_sub_type
when 0 then 'unspecified'
when 1 then 'binary'
when 3 then 'acl'
else
case
when f.rdb$field_sub_type is null then 'unspecified'
end
end
when 37 then
case f.rdb$field_sub_type
when 0 then 'unspecified'
when 1 then 'text'
when 3 then 'acl'
else
case
when f.rdb$field_sub_type is null then 'unspecified'
end
end
when 261 then
case f.rdb$field_sub_type
when 0 then 'unspecified'
when 1 then 'text'
when 2 then 'blr'
when 3 then 'acl'
when 4 then 'reserved'
when 5 then 'encoded-meta-data'
when 6 then 'irregular-finished-multi-db-tx'
when 7 then 'transactional_description'
when 8 then 'external_file_description'
end
end as "ActualSubType"
from rdb$fields f
left join rdb$types t
on t.rdb$type = f.rdb$field_type
and t.rdb$field_name = 'RDB$FIELD_TYPE'
left join rdb$types st
on st.rdb$type = f.rdb$field_sub_type
and st.rdb$field_name = 'RDB$FIELD_SUB_TYPE'
order by
f.rdb$field_type, t.rdb$type_name, f.rdb$field_sub_type
The Wiert Corner - irregular stream of stuff 
Leave a comment