Table description of firebird

Hello

This question is an extension of the the question posted on StackOverFlow @
delphi - How can I get the table description (fields and types) from Firebird with dbExpress - Stack Overflow

I have used his SQL to build a query in embedded firebird:

SELECT
  RF.RDB$RELATION_NAME,
  RF.RDB$FIELD_NAME FIELD_NAME,
  RF.RDB$FIELD_POSITION FIELD_POSITION,
  CASE F.RDB$FIELD_TYPE
    WHEN 7 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'SMALLINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 8 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'INTEGER'
        WHEN 1 THEN 'NUMERIC('  || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 9 THEN 'QUAD'
    WHEN 10 THEN 'FLOAT'
    WHEN 12 THEN 'DATE'
    WHEN 13 THEN 'TIME'
    WHEN 14 THEN 'CHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ') '
    WHEN 16 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'BIGINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 27 THEN 'DOUBLE'
    WHEN 35 THEN 'TIMESTAMP'
    WHEN 37 THEN
     IIF (COALESCE(f.RDB$COMPUTED_SOURCE,'')<>'',
      'COMPUTED BY ' || CAST(f.RDB$COMPUTED_SOURCE AS VARCHAR(250)),
      'VARCHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')')
    WHEN 40 THEN 'CSTRING' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
    WHEN 45 THEN 'BLOB_ID'
    WHEN 261 THEN 'BLOB SUB_TYPE ' || F.RDB$FIELD_SUB_TYPE
    ELSE 'RDB$FIELD_TYPE: ' || F.RDB$FIELD_TYPE || '?'
  END FIELD_TYPE,
  IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0, NULL, 'NOT NULL') FIELD_NULL,
  CH.RDB$CHARACTER_SET_NAME FIELD_CHARSET,
  DCO.RDB$COLLATION_NAME FIELD_COLLATION,
  COALESCE(RF.RDB$DEFAULT_SOURCE, F.RDB$DEFAULT_SOURCE) FIELD_DEFAULT,
  F.RDB$VALIDATION_SOURCE FIELD_CHECK,
  RF.RDB$DESCRIPTION FIELD_DESCRIPTION
FROM RDB$RELATION_FIELDS RF
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID))
WHERE (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0)
ORDER BY RF.RDB$RELATION_NAME,RF.RDB$FIELD_POSITION;

However i would like to extend the SQL to include column Descriptions as shown here:

Is there a way to do this? Thank you again for all of your assistance.

Hello,

While this may have worked in the past to some extent in some DB’s ( see Base: MySQL table field comments: description vs column description ), current ‘quick’ tests of MySQL, PostgreSQL and Firebird embedded shows this not working.

The SQL you have does have provisions for displaying a description but not from the GUI. Tested using Firebird comment and it was included in the query:

1 Like

This might be the solution I need. Thank you.

I will go the suggested rout for now, but I would still like to know where is the description stored when it is entered through the interface. Thank you.

It is stored in a file within the Base .odb - content.xml:

Base:

Screenshot at 2022-06-27 09-58-17

Content.xml:

<db:columns><db:column db:name="id" db:help-message="Line1"/><db:column db:name="name" db:help-message="Line2"/><db:column db:name="series_link" db:help-message="Line3"/></db:columns

.
Edit:
.
Did find access to this using MRI:
.
Screenshot at 2022-06-27 13-27-17
.
As this is not a part of the database, SQL could not access this. Macros could (image has code generated by MRI).

1 Like

This looks like a better solution. Thank you yet again.