14

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']

jramirez
  • 8,537
  • 7
  • 33
  • 46

3 Answers3

32

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

jramirez
  • 8,537
  • 7
  • 33
  • 46
  • 5
    You may also want to add this order by clause to get the field names in positional order: order by rdb$field_position – dave-holm May 02 '14 at 20:51
  • 1
    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; – Andrew Dennison Apr 06 '16 at 13:17
1

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
pigmej
  • 169
  • 5
1

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%';
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ladislav Schnaiberg
  • 11
  • 1