-- This script will create a SQL statement to create indexes from the schema.
-- Primary keys, unique keys, foreign keys and general indexes are rebuilt.
-- Mark Reineck 12-19-1997

set feedback off
set head off
set echo off
set recsep off
set pages 50000
set lines 150

-- These temporary views are used to determine when we are at the last field
-- in the table.

create view idxcol as select index_name, count(*) as columns from user_ind_columns group by index_name;

create view fgncol as select constraint_name, count(*) as columns from user_cons_columns group by constraint_name;

-- Write the script to a file, otherwise it is useless

spool mkindex.sql

-- Create primary keys

select
decode(c.column_position,1,'alter table '||i.table_name||' add primary key ('),
c.column_name,
decode(c.column_position,a.columns, ') using index inittrans '||ini_trans||' maxtrans '||max_trans||' tablespace '||tablespace_name|| ' pctfree '||pct_free||' storage (initial '||initial_extent||' next '||next_extent||' max_extents '|| max_extents||' pctincrease '||pct_increase||');' ,',')
from user_indexes i, user_ind_columns c, user_constraints x, idxcol a
where c.index_name=i.index_name and c.index_name=x.constraint_name(+) and a.index_name=c.index_name and x.constraint_type='P' order by c.table_name, c.index_name, c.column_position;

-- Create unique keys

select
decode(c.column_position,1,'alter table '||i.table_name||' add constraint '||x.constraint_name||' unique ('),
c.column_name,
decode(c.column_position,a.columns, ') using index inittrans '||ini_trans||' maxtrans '||max_trans||' tablespace '||tablespace_name|| ' pctfree '||pct_free||' storage (initial '||initial_extent||' next '||next_extent||' max_extents '|| max_extents||' pctincrease '||pct_increase||');' ,',')
from user_indexes i, user_ind_columns c, user_constraints x, idxcol a
where c.index_name=i.index_name and c.index_name=x.constraint_name(+) and a.index_name=c.index_name and x.constraint_type='U'
order by c.table_name, c.index_name, c.column_position;

-- Create other indexes

select
decode(c.column_position,1,'create index '||i.index_name||' on '||i.table_name||' ('),
c.column_name,
decode(c.column_position,a.columns, ') using index inittrans '||ini_trans||' maxtrans '||max_trans||' tablespace '||tablespace_name|| ' pctfree '||pct_free||' storage (initial '||initial_extent||' next '||next_extent||' max_extents '|| max_extents||' pctincrease '||pct_increase||');' ,',')
from user_indexes i, user_ind_columns c, idxcol a
where c.index_name=i.index_name and a.index_name=c.index_name and c.index_name not in (select constraint_name from user_constraints)
order by c.table_name, c.index_name, c.column_position;

-- Create foreign keys

select
decode(a.position,1,decode(b.position,1, 'alter table '||c.table_name||' add constraint '||c.constraint_name|| ' foreign key ('),','),
decode(b.position,1,a.column_name||decode(a.position,y.columns, ') references '||b.table_name||' (')),
decode(a.position,y.columns,b.column_name|| decode(b.position,z.columns,');'))
from user_cons_columns a, user_cons_columns b, user_constraints c, fgncol y, fgncol z
where a.constraint_name=c.constraint_name and b.constraint_name=c.r_constraint_name and y.constraint_name=a.constraint_name and z.constraint_name=b.constraint_name and (b.position=1 or a.position=y.columns)
order by b.position, a.position;

spool off;

-- Remove the view, we're done with it, then reset some stuff

drop view idxcol;
drop view fgncol;

set feedback on;
set head on;
set echo on;