CODEWITHSUNDEEP
firebirddatabase-metadata
Maveron
Maveron

Reputation: 71

Getting all tables / columns and their datatype firebird in one query

I want to query the following: all tables, with their columns and data type

I got this so far from http://www.firebirdfaq.org/faq174/

select f.rdb$relation_name, f.rdb$field_name, f.rdb$field_source
from rdb$relation_fields f
join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name
and r.rdb$view_blr is null 
and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
order by 1, f.rdb$field_position

and I just added the above code "f.rdb$field_source" to show me also that info on that table, that should allow me to find the following datatype of the columns in the this table RDB$FIELDS

SELECT * from  RDB$FIELDS

I want to add in the above query something like this:

select f.rdb$relation_name, f.rdb$field_name, f.rdb$field_source
from rdb$relation_fields f
join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name
and r.rdb$view_blr is null 
and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
order by 1, f.rdb$field_position

Plus then the following needs to happen:

select  a.RDB$FIELD_TYPE from RDB$FIELDS 
where RDB$FIELD_NAME is equal to the .rdb$field_source 
from rdb$relation_fields

So i have one table with Tablename/Columnname/TypeofData.

I got little experience in SQL. I hope my question is clear enough

Upvotes: 3

Views: 5372

Answers (1)

Andrej Kirejeŭ
Andrej Kirejeŭ

Reputation: 5491

Just join RDB$FIELDS table:

SELECT
  R.RDB$RELATION_NAME,
  R.RDB$FIELD_NAME,
  R.RDB$FIELD_SOURCE,
  F.RDB$FIELD_LENGTH,
  F.RDB$FIELD_TYPE,
  F.RDB$FIELD_SCALE,
  F.RDB$FIELD_SUB_TYPE
FROM
  RDB$RELATION_FIELDS R
  JOIN RDB$FIELDS F
    ON F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE
  JOIN RDB$RELATIONS RL
    ON RL.RDB$RELATION_NAME = R.RDB$RELATION_NAME
WHERE
  COALESCE(R.RDB$SYSTEM_FLAG, 0) = 0
  AND
  COALESCE(RL.RDB$SYSTEM_FLAG, 0) = 0
  AND
  RL.RDB$VIEW_BLR IS NULL
ORDER BY
  R.RDB$RELATION_NAME,
  R.RDB$FIELD_POSITION

Upvotes: 7

Related Questions