How to find out whether a field has set a default value

Maximiliano Robaina wrote:

If I want to drop a column-level default, in firebird 2.x I can do:

ALTER TABLE tablename ALTER [COLUMN] colname DROP DEFAULT

But, if [COLUMN] has not a default value setting, that statement raise an error. The question is, How can I to know if a field has set a default value?

Query against system tables? Which one?

Thomas Steinmaurer answers:

The following gives you all fields for a given relation and a separate field if the field has a default value. Change the query for your needs.

select
   rf.rdb$relation_name
   , rf.rdb$field_name
   , case
       when f.rdb$default_value is not null then 1 else 0
     end has_default
from
   rdb$fields f join rdb$relation_fields rf on f.rdb$field_name =
rf.rdb$field_source
where
   rf.rdb$relation_name = <your_relation>

Maximiliano Robaina replied:

What is the difference between rdb$relation_fields.default_value and rdb$fields.default_value ?

Ann W. Harrison answers:

The RDB$FIELDS table is equivalent to SQL Domains - meta definitions for field types. When the system tables for Firebird were created, SQL was not the only game in town. Under the rules of the day and place, every column had to be based on a domain. When SQL DDL rules were added to InterBase, that rule was kept, and when a column was defined directly, without reference to a domain, InterBase and now Firebird creates a dummy domain.

RDB$RELATION_FIELDS is the mapping between the domain and a column in a table. The default value is one of the attributes of a domain that can be changed when defining a column based on a domain.