create & drop index definitions generator

{
You can generate all data definitions present in a database using dbschema utility. However, dbschema does not provide any switch to generate *only* the index creation definitions. Further, in the generated full schema output the index creation definitions are dumped in multiple lines, making it difficult to extract those lines selectively.

The SQL script below generates two sql scripts:
1. drop_index.sql and
2. create_index.sql

This script gathers information from catalog tables - systables and sysindexes. The IN clause of the create index syntax need to be replaced with a valid one that applicable to your setup.

It is to be noted, that the object modes for Unique/Duplicate indexes are ignored. This script also does not considers index fragmentation scheme if any.

Regards,
Shriyan
}

--create & drop index definitions generator
--Author : V.P. Shriyan
--Date : 05-MAR-2004
--modified on: 31-MAR-2004

set isolation to dirty read;

unload to "drop_index.sql" delimiter
select "drop index ",' "'||trim(i.owner)||'".',trim(i.idxname)," ;"
from systables s, sysindexes i
where s.tabid=i.tabid
and idxname[1,1] !=" "
and s.tabid >99 and s.tabtype="T" ;

create temp table x
(
idxname varchar(64), owner varchar(8) , tabid integer ,
idxtype char(1) , clustered char(1) , p1 varchar(64),
p2 varchar(64), p3 varchar(64), p4 varchar(64),
p5 varchar(64), p6 varchar(64), p7 varchar(64),
p8 varchar(64), p9 varchar(64), p10 varchar(64),
p11 varchar(64), p12 varchar(64), p13 varchar(64),
p14 varchar(64), p15 varchar(64), p16 varchar(64)
) with no log;

insert into x
(
idxname , owner , tabid , idxtype , clustered , p1 ,
p2 , p3 , p4 , p5 , p6 , p7 ,
p8 , p9 , p10 , p11 , p12 , p13,
p14 , p15 , p16
)
select
i.idxname , i.owner , i.tabid,
i.idxtype , i.clustered , decode(i.part1,0,null),
decode(i.part2,0,null) , decode(i.part3,0,null), decode(i.part4,0,null),
decode(i.part5,0,null) , decode(i.part6,0,null) , decode(i.part7,0,null),
decode(i.part8,0,null) , decode(i.part9,0,null) , decode(i.part10,0,null),
decode(i.part11,0,null), decode(i.part12,0,null), decode(i.part13,0,null),
decode(i.part14,0,null), decode(i.part15,0,null), decode(i.part16,0,null)
from systables s, sysindexes i
where s.tabid=i.tabid
and idxname[1,1] !=" "
and s.tabid >99 and s.tabtype="T" ;

update x set p1= (select trim(colname)||case when part1<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part1 or colno=i.part1*-1)) where p1 != 0;

update x set p2= (select trim(colname)||case when part2<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part2 or colno=i.part2*-1)) where p2 != 0;

update x set p3= (select trim(colname)||case when part3<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part3 or colno=i.part3*-1)) where p3 != 0;

update x set p4= (select trim(colname)||case when part4<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part4 or colno=i.part4*-1)) where p4 != 0;

update x set p5= (select trim(colname)||case when part5<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part5 or colno=i.part5*-1)) where p5 != 0;

update x set p6= (select trim(colname)||case when part6<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part6 or colno=i.part6*-1)) where p6 != 0;

update x set p7= (select trim(colname)||case when part7<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part7 or colno=i.part7*-1)) where p7 != 0;

update x set p8= (select trim(colname)||case when part8<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part8 or colno=i.part8*-1)) where p8 != 0;

update x set p9= (select trim(colname)||case when part9<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part9 or colno=i.part9*-1)) where p9 != 0;

update x set p10= (select trim(colname)||case when part10<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part10 or colno=i.part10*-1)) where p10 != 0;

update x set p11= (select trim(colname)||case when part11<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part11 or colno=i.part11*-1)) where p11 != 0;

update x set p12= (select trim(colname)||case when part12<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part12 or colno=i.part12*-1)) where p12 != 0;

update x set p13= (select trim(colname)||case when part13<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part13 or colno=i.part13*-1)) where p13 != 0;

update x set p14= (select trim(colname)||case when part14<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part14 or colno=i.part14*-1)) where p14 != 0;

update x set p15= (select trim(colname)||case when part15<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part15 or colno=i.part15*-1)) where p15 != 0;

update x set p16= (select trim(colname)||case when part16<0 then ' DESC' else '' end from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and (colno=i.part16 or colno=i.part16*-1)) where p16 != 0;

update x set idxname=trim(idxname), owner=trim(owner), p1=trim(p1), p2=trim(p2), p3=trim(p3), p4=trim(p4), p5=trim(p5), p6=trim(p6), p7=trim(p7), p8=trim(p8), p9=trim(p9), p10=trim(p10), p11=trim(p11), p12=trim(p12), p13=trim(p13), p14=trim(p14), p15=trim(p15), p16=trim(p16);

unload to 'create_index.sql' delimiter
select "CREATE",
decode(idxtype,"U"," UNIQUE"," "),
decode(clustered,"C"," CLUSTER "," "),
" INDEX "," ",
'"'||x.owner||'".', idxname,
" ON ", " ",
'"'||x.owner||'".', tabname, " ",
"(",
case
when p16 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8||","||p9||","||p10||","||p11||","||p12||","||p13||","||p14||","||p15||","||p16
when p15 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8||","||p9||","||p10||","||p11||","||p12||","||p13||","||p14||","||p15
when p14 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8||","||p9||","||p10||","||p11||","||p12||","||p13||","||p14
when p13 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8||","||p9||","||p10||","||p11||","||p12||","||p13
when p12 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8||","||p9||","||p10||","||p11||","||p12
when p11 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8||","||p9||","||p10||","||p11
when p10 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8||","||p9||","||p10
when p9 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8||","||p9
when p8 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7||","||p8
when p7 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6||","||p7
when p6 is not null then
p1||","||p2||","||p3||","||p4||","||p5||","||p6
when p5 is not null then
p1||","||p2||","||p3||","||p4||","||p5
when p4 is not null then
p1||","||p2||","||p3||","||p4
when p3 is not null then
p1||","||p2||","||p3
when p2 is not null then
p1||","||p2
else p1
end,
") IN xspace;"
from x,systables s where x.tabid=s.tabid;