12

So I've got a couple of Firebird databases I need to convert. I've managed to connect to them using python code and the kinterbasdb library, but I can't find a way to get a list of all the tables in the database. Is there a command that will give me the table names?

Beacon80
  • 349
  • 1
  • 4
  • 14

3 Answers3

30

Getting the list of tables is:

  1. In isql: show tables;
  2. As a normal query: 
    SELECT a.RDB$RELATION_NAME
FROM RDB$RELATIONS a
WHERE COALESCE(RDB$SYSTEM_FLAG, 0) = 0 AND RDB$RELATION_TYPE = 0
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • The second method worked, although I had to drop the Relation Type from the equation – Beacon80 Feb 13 '14 at 22:05
  • 1
    @Beacon80 I use Firebird 2.5, this column isn't available in older versions (database structure versions); if you use Firebird 2.5 and don't have this column, you need to backup and restore the database. – Mark Rotteveel Feb 13 '14 at 22:06
  • 2
    The query by Brent Rowland works for older versions (my query without `RDB$RELATION_TYPE = 0` also includes views). – Mark Rotteveel Feb 14 '14 at 06:33
  • `RDB$SYSTEM_FLAG = 0` - shouldn't there also be a check for `NULL` not only zero ? I think there is some ambiguity there, when you think about differesnt legacy engine and ODS versions – Arioch 'The Aug 25 '20 at 20:12
  • @Arioch'The Good point, IIRC, it should always be populated in 2.5 database, but I have been bitten by that before. Added a `COALESCE`... – Mark Rotteveel Aug 25 '20 at 20:30
  • If my memory serves me (not sure), it is NOT always populated when engine 2.5 opens ODS of 2.1 (AFAIR that is 11.1). – Arioch 'The Aug 26 '20 at 08:37
6

I use:

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE (RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL) AND RDB$VIEW_BLR IS NULL
ORDER BY RDB$RELATION_NAME;
Brent Rowland
  • 121
  • 2
1

I use that :

select rdb$relation_name, rdb$description
  from rdb$relations
 where rdb$view_blr is null
   and (rdb$system_flag is null or rdb$system_flag = 0)
 order by 1;
Rufflito
  • 96
  • 5