I can find a way of doing this using HSQLDB but not firebird. I think I ideally want an information_schema (?)
I found some SQL to do it from a firebird site but it crashed when I tried to run it in LO (under tools, SQL).
I found a different query/statement, which did run but it didn’t give me the table properties, just the field names of all my tables.
I want a text or sql list of the fields and their properties from individual tables.
I’m having problems setting up relationships and think something like this might be helpful, help me find out the discrepancies. (also I had the relationships working in another almost identical database so I could compare the information from that with the one I am having problems with.)
Thanks
Hello,
List of field names for all tables:
select f.rdb$relation_name, f.rdb$field_name
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;
List of table info (need to specify table name):
SELECT r.RDB$FIELD_NAME AS field_name,
r.RDB$DESCRIPTION AS field_description,
r.RDB$DEFAULT_VALUE AS field_default_value,
r.RDB$NULL_FLAG AS field_not_null_constraint,
f.RDB$FIELD_LENGTH AS field_length,
f.RDB$FIELD_PRECISION AS field_precision,
f.RDB$FIELD_SCALE AS field_scale,
CASE f.RDB$FIELD_TYPE
WHEN 261 THEN 'BLOB'
WHEN 14 THEN 'CHAR'
WHEN 40 THEN 'CSTRING'
WHEN 11 THEN 'D_FLOAT'
WHEN 27 THEN 'DOUBLE'
WHEN 10 THEN 'FLOAT'
WHEN 16 THEN 'INT64'
WHEN 8 THEN 'INTEGER'
WHEN 9 THEN 'QUAD'
WHEN 7 THEN 'SMALLINT'
WHEN 12 THEN 'DATE'
WHEN 13 THEN 'TIME'
WHEN 35 THEN 'TIMESTAMP'
WHEN 37 THEN 'VARCHAR'
ELSE 'UNKNOWN'
END AS field_type,
f.RDB$FIELD_SUB_TYPE AS field_subtype,
coll.RDB$COLLATION_NAME AS field_collation,
cset.RDB$CHARACTER_SET_NAME AS field_charset
FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
WHERE r.RDB$RELATION_NAME = 'YOUR_ABLE_NAME_HERE' -- table name
ORDER BY r.RDB$FIELD_POSITION;
Edit:
Another you may be interested in. Lists Primary and Foreign key fields:
select r.rdb$constraint_type,
i.rdb$field_name
from rdb$relation_constraints r, rdb$index_segments i
where r.rdb$relation_name='YOUR_TABLE_NAME_HERE' and
(r.rdb$constraint_type='PRIMARY KEY'
or r.rdb$constraint_type='FOREIGN KEY') and
r.rdb$index_name=i.rdb$index_name;
Here are some more → https://edn.embarcadero.com/article/25259
Lots out there. Just a bit of searching.
As with all questions, if this answers your question please tick the 
(upper left area of answer). It helps others to know there was an accepted answer.
Thanks - they seem to be the same as the ones I tried.
It was the second one that caused the crash (think it might just have timed out) but I’ll try again in case there was any error in the code I copied online. I’ll update if I get it to work.
Thanks again for all your help - fingers crossed I will be able to get something to work soon … (my broadband has started dropping out now …so I’ll post the bug from my other post tomorrow …and then try this - for my sanity I really have to step away from it for a good few hours)
Thanks again
Tested all three before posting. No problem. Have used in past a number of times without problem on various Linux systems with different LO versions. At this point it appears you have some problems with you system. As for LO, you can always try resetting the user profile → LibreOffice user profile
Edit:
Make sure when before running, turn on Run SQL command directly. Can do from toolbar icon or menu → Edit->Run SQL command directly.
Thanks - it worked. (think run SQL directly helped)
I’d rather It was text - I can only see it as a table.
Also the field collation is giving me approx 50 versions of each field that is varchar format -honestly I don’t really know what field collation is … (but I ran it on a quick test database and it was the same for that) so I don’t think that is my problem. Might try and edit the sql so it doesn’t list them so I can more easily see the field properties. But so far can’t see any discrepancies
My Boolean fields are showing as ‘unknown’ - which I can see would be expected and I don’t think they are causing the problem.
The primary/foreign key one shows what I would expect if the tables were linked. So still have a problem. But I now have something to work with.
(I am actually thinking I might be better/quicker to start a new database, create the tables from scratch and then copy just the sql queries from the other database)
Thanks again for all your help (and understanding)
This is just to say I’ve tried this in Firebird…
Windows 10/ Libre Office 7.1.5.2, Base, using embedded Firebird, 7 Sep 21
…and it worked for me, mostly, if you bear in mind a few “gotchas”…
-
Remember that names of tables, and fields are case sensitive.
-
DON’T try the suggested commands via the SQL command line interface, the one you can get from the menu on the database’s main project manager window, “Tools/SQL”. (If you DO want to work there, at least remember to tick “Show output of “Select” statements”! I say “don’t” because a) they execute very slowly… even for small tables, the system will “go away”, say “not responding” for tens of seconds for a two field table, tens of minutes for a 15 field table) and b) Will give you “uncaptioned” results.)
2 (continued ) )… DO USE the “Query” “module”, task “Create Query in SQL view”, and tick "Run SQL Command Directly on the “Edit” menu item of the “Create Query” dialog that opens. I THINK you can put virtually any SQL “in” by this channel, i.e. you are not limited JUST to SQL to generate a query result.
-
Remember to fix the bit needed in some of these where it says ‘YOUR_TABLE_NAME_HERE’ (or, due to a small typo in one, ‘YOUR_ABLE_NAME_HERE’… don’t let no result from a “search” on “table” fool you!)
-
Remember that while some things DON’T (for me at least) work inside the “Command to execute:” box of the command line tool, ctrl-A, ctrl-C, ctrl-V, the “arrow keys”, etc, DO work. (I think it was mainly mouse-driven text selection that my command line interface didn’t “see”.)
So- Gotchas out of the way…
===
The one to list all fields, WORKED for me on FDB051 FrmToCode… but produced 105 lines for a two field table. Here are some of the variants which appear. In all other fields, the values are the same for each variant. The variation is just in the “Field_Collation” column. I imagine it is somehow connected with how I created the table in the first place.
Do not understand all your issues. These items (just re-tested) all work in the Query section (turn on Run SQL command directly) without issue or big time delays. These statements each ran in a second or two on my system.
Many thanks for continuing the “journey” with me.
You’re right! (Of course!)… and you spotted what we were doing differently, at least the thing that fixes my speed-of-execution issue. (And, I would hope, that of the earlier poster who took the long interval of “not responding” as a “crash”.)
I’ve edited my earlier post, in light of the help in your post above. (In hopes that our work can make things easier for Those Who Come After this adventure.)