Hello Maxi,

>>> 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?
>>
>> 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>
>>
>> Hope this helps.
>
> What is the difference between rdb$relation_fields.default_value and rdb
> $fields.default_value ?

Good catch! Ann gave you the explanation.

This an adjusted version of the system table query:

select
rf.rdb$relation_name
, rf.rdb$field_name
, case
when
(rf.rdb$default_value is not null)
or (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_relatioin>
order by
rf.rdb$field_position



--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/