4

I'm working on JSF application that uses a Firebird 3.0 database containing hundreds of tables. I need to delete all tables time to time.

I have checked this query:

DROP TABLE TABLE_NAME

but only one table can be deleted at a time by using this query and its very time consuming for program, can I have another approach to hammer it away?

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
Nitin Upadhyay
  • 101
  • 12
  • 1
    You really need to answer the why though, because it really sounds like a flawed approach. If you really need to regularly deleted all tables, then you'd be better off dropping the entire database and creating a new empty one from a backup, or using a different approach to how you manage your data. Because dropping all your tables is pretty weird. – Mark Rotteveel May 10 '19 at 08:11
  • Yes @MarkRotteveel , dropping entire database also can be a solution. but can you please let me know in if i use spring boot + Hibernate with firebird database then how can i mention createDatabaseIfNotExist like MySql. As i need to create database automatically on boot for spring project. – Nitin Upadhyay May 10 '19 at 09:06
  • There is no direct equivalent (yet), but you can use [`org.firebirdsql.management.FBManager`](https://www.firebirdsql.org/file/documentation/drivers_documentation/java/3.0.x/docs/org/firebirdsql/management/FBManager.html) to create a database for you. – Mark Rotteveel May 10 '19 at 09:39

1 Answers1

3

You can create procedure in which drop tables

create or alter procedure PRC_DROP_TABLES
 as
declare variable TBL varchar(50);

begin
 for select r.rdb$relation_name
     from rdb$relation_fields r
     where
     r.rdb$system_flag=0 and r.rdb$view_context is null
     -- and   r.rdb$relation_name not containing  '$' --uncomment and modify this if you what filter tables by condition 
     group by   r.rdb$relation_name
     into :tbl do
     execute statement  'drop table '||:tbl;
end
Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
Konstantin Streletsky
  • 311
  • 1
  • 7
  • 1
    You can also do this with [`execute block`](https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-execblock.html) – Mark Rotteveel May 10 '19 at 10:53
  • `where r.rdb$system_flag=0` - that is fragile, cause that field in many system tables can be `NULL` also. Correct clause would be `where coalesce(r.rdb$system_flag,0)=0` or `where ( (r.rdb$system_flag is NULL) or (r.rdb$system_flag=0) )` – Arioch 'The May 16 '19 at 13:02
  • 1
    `for select r.rdb$relation_name from rdb$relation_fields r` - this query has two mistakes. 1) it does not tell VIEWS from TABLES (but maybe it is good, maybedropping views together with tables is what topic starter needs) and 2) it will fail on any table having more than one column. – Arioch 'The May 16 '19 at 13:06