Get list of column names from a Firebird database table

Question

How do you get a list of the column names in an specific table?

ie.

Firebird table:

| name | id | phone_number |

get list like this:

columnList = ['name', 'id', 'phone_number']

Ivie

Answer 1

if you want to get a list of column names in an specific table, this is the sql query you need:

select rdb$field_name from rdb$relation_fields
where rdb$relation_name='YOUR-TABLE_NAME';

I tried this in firebird 2.5 and it works.

the single quotes around YOUR-TABLE-NAME are necessary btw

Ivie

You may also want to add this order by clause to get the field names in positional order: order by rdb$field_position – Schnorr

Also note that Firebird table names are all uppercase. So if your table is called CUSTOMERS, the SQL statement is select rdb$field_name from rdb$relation_fields where rdb$relation_name='CUSTOMERS' order by rdb$field_position; – Cheffetz

Answer 2

Get list of columns (comma-separated, order by position) for all table:

SELECT RDB$RELATION_NAME AS TABLE_NAME, list(trim(RDB$FIELD_NAME),',') AS COLUMNS
FROM RDB$RELATIONS
LEFT JOIN (SELECT * FROM RDB$RELATION_FIELDS ORDER BY RDB$FIELD_POSITION) USING (rdb$relation_name)
WHERE
(RDB$RELATIONS.RDB$SYSTEM_FLAG IS null OR RDB$RELATIONS.RDB$SYSTEM_FLAG = 0)
AND RDB$RELATIONS.rdb$view_blr IS null 
GROUP BY RDB$RELATION_NAME
ORDER BY 1

Sos

Answer 3

Working well for check YOUR-COLUMN_NAME_fragment in database tables , used in DBeaver on FB 3.0.7

select 
    RDB$FIELD_NAME AS "COLUMN", 
    RDB$RELATION_NAME AS "TABLE" 
from 
    rdb$relation_fields
where 
    RDB$FIELD_NAME like '%YOUR-COLUMN_NAME_fragment%';

Shani