ODP: [firebird-support] Re: is it possible to drop all indexes, except those related to PKand FK?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

If you join with contraints you have better control.
An personally i prefere easier way, like simple select.
If you really need to delete all instead of PK and FK you should not exclude 
uniques as you ommit custom unique indexes.

SELECT
'DROP INDEX ' || TRIM(I.RDB$INDEX_NAME) || ';', RC.RDB$CONSTRAINT_TYPE
FROM
RDB$INDICES I
LEFT JOIN RDB$RELATION_CONSTRAINTS RC ON RC.RDB$CONSTRAINT_NAME = 
I.RDB$INDEX_NAME
WHERE
I.RDB$SYSTEM_FLAG <> 1
AND RC.RDB$CONSTRAINT_TYPE NOT IN ('PRIMARY KEY', 'FOREIGN KEY') 
–eventually add also ‘UNIQUE’ here if you really need

regards,
Karol Bieniaszewski

[firebird-support] Ubuntu 3.0.5 version

[Jorge Andres Brugger jorge.brug...@gmail.com [firebird-support]]

Mariuz's Ubuntu repository contains version 3.0.5.33100 and was released in
October 2019. The official version on the Firebird website is 3.0.5.33220,
released in January 2020. This is ok in terms of stability, if I want to
use the Ubuntu package?
Thanks

[firebird-support] Re: is it possible to drop all indexes, except those related to PK and FK?

[hamacker sirhamac...@gmail.com [firebird-support]]

response to my self, is it correct?

execute block
  returns (drop_index_name varchar(31), dropped boolean) as
declare variable doit boolean;
declare variable stmt varchar(2048);
begin
  dropped=false; -- test before
  for
select i.rdb$index_name
from rdb$indices i
where
  (i.rdb$system_flag<>1)
  and (i.rdb$unique_flag<>1)
  and (rdb$foreign_key is null) into :drop_index_name do
   begin
 stmt='drop index '||:drop_index_name||';';
 if (dropped) then
   execute statement :stmt;
 suspend;
   end
end


Em qui., 12 de mar. de 2020 às 17:59, hamacker 
escreveu:

>
> Is it possible to drop all indexes, except those related to PK and FK?
>
> I would like to do a test and recreate them by script.
>
>