InterBase/Firebird – query to show which fields in your database are not based on a domain
Posted by jpluimers on 2009/08/13
Our strategy is that in all of our InterBase and Firebird databases, the column definitions are based on domains.
But some of our databases do not have that for all columns (usually because we got involved later in the development cycle).
When you forget to define a column based on a domain, then both InterBase and Firebird will create a new system domain on the fly that starts with ‘RDB$’.
So if you forget, you get a new system domain for every column!
The below query will list those columns.
select
rf.rdb$relation_name, rf.rdb$field_name,
rf.rdb$field_source,
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$relation_fields rf
join rdb$fields f
on f.rdb$field_name = rf.rdb$field_source
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'
where
rf.rdb$system_flag = 0
and rf.rdb$field_source like 'RDB$%'
order by
rf.rdb$relation_name,
rf.rdb$field_position
This query is based on some postings from Felix Colibri and Lorenzo Alberton and input from my colleague Edwin van der Kraan. Thanks guys!
The EMPLOYEE sample database that ships with both Firebird and InterBase, is partially based on domains, so it is a good DB to show what results this query will get you.
When running the query on the EMPLOYEE database, you will see something like the table below.
The RDB$FIELD_SOURCE column contains the system generated domains (that all start with ‘RDB$’).
The ActualType and ActualSubType columns will help you defining the correct domains.
If you change the ordering to be f.rdb$field_type, t.rdb$type_name, f.rdb$field_sub_type, f.rdb$field_length, then you get similar domains ordered together.
Note that you do not always get a rdb$type_name value, you can see it in the below table for RDB$31 domain (table SALES, column AGED) which is a calculated field.
You could filter away those by joining on the rdb$relation_fields table, and check for the rdb$update_flag to be non zero.
RDB$RELATION_NAME RDB$FIELD_NAME RDB$FIELD_SOURCE RDB$FIELD_TYPE RDB$TYPE_NAME RDB$FIELD_SUB_TYPE RDB$CHARACTER_LENGTH RDB$FIELD_SCALE RDB$FIELD_LENGTH RDB$SUB_TYPE_NAME ActualType ActualSubType --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- COUNTRY CURRENCY RDB$1 37 VARYING 0 10 0 10 varchar unspecified CUSTOMER CUSTOMER RDB$18 37 VARYING 0 25 0 25 varchar unspecified CUSTOMER CITY RDB$19 37 VARYING 0 25 0 25 varchar unspecified CUSTOMER STATE_PROVINCE RDB$20 37 VARYING 0 15 0 15 varchar unspecified CUSTOMER POSTAL_CODE RDB$21 37 VARYING 0 12 0 12 varchar unspecified CUSTOMER ON_HOLD RDB$22 14 TEXT 0 1 0 1 char unspecified DEPARTMENT DEPARTMENT RDB$5 37 VARYING 0 25 0 25 varchar unspecified DEPARTMENT LOCATION RDB$6 37 VARYING 0 15 0 15 varchar unspecified EMPLOYEE PHONE_EXT RDB$7 37 VARYING 0 4 0 4 varchar unspecified EMPLOYEE HIRE_DATE RDB$8 35 TIMESTAMP 0 0 8 timestamp EMPLOYEE FULL_NAME RDB$9 37 VARYING 0 0 0 37 varchar unspecified JOB JOB_TITLE RDB$2 37 VARYING 0 25 0 25 varchar unspecified JOB JOB_REQUIREMENT RDB$3 261 BLOB 1 0 8 TEXT blob text JOB LANGUAGE_REQ RDB$4 37 VARYING 0 15 0 15 varchar unspecified PHONE_LIST PHONE_EXT RDB$7 37 VARYING 0 4 0 4 varchar unspecified PHONE_LIST LOCATION RDB$6 37 VARYING 0 15 0 15 varchar unspecified PROJECT PROJ_NAME RDB$10 37 VARYING 0 20 0 20 varchar unspecified PROJECT PROJ_DESC RDB$11 261 BLOB 1 0 8 TEXT blob text PROJ_DEPT_BUDGET FISCAL_YEAR RDB$12 8 LONG 0 0 4 integer PROJ_DEPT_BUDGET QUART_HEAD_CNT RDB$13 8 LONG 0 0 4 integer SALARY_HISTORY CHANGE_DATE RDB$14 35 TIMESTAMP 0 0 8 timestamp SALARY_HISTORY UPDATER_ID RDB$15 37 VARYING 0 20 0 20 varchar unspecified SALARY_HISTORY PERCENT_CHANGE RDB$16 27 DOUBLE 0 0 8 double SALARY_HISTORY NEW_SALARY RDB$17 27 DOUBLE 0 0 8 double SALES ORDER_STATUS RDB$23 37 VARYING 0 7 0 7 varchar unspecified SALES ORDER_DATE RDB$24 35 TIMESTAMP 0 0 8 timestamp SALES SHIP_DATE RDB$25 35 TIMESTAMP 0 0 8 timestamp SALES DATE_NEEDED RDB$26 35 TIMESTAMP 0 0 8 timestamp SALES PAID RDB$27 14 TEXT 0 1 0 1 char unspecified SALES QTY_ORDERED RDB$28 8 LONG 0 0 4 integer SALES TOTAL_VALUE RDB$29 8 LONG 2 -2 4 BLR integer decimal SALES DISCOUNT RDB$30 10 FLOAT 0 0 4 float SALES AGED RDB$31 16 0 -9 8 int64 bigint
This helped us a lot when cleaning up a couple of databases.
–jeroen
Firebird News » Firebird – query to show which fields in your database are not based on a domain said
[...] below query will list those columns. Permalink Leave your comment [...]