Auto generate Index scripts

[Mark Spritzler]

Here is a procedure that I wrote along with a couple of queries that will use the procedure to create Index creation scripts. The where clause in the queries are specific to my app, so you will have to change them

 CREATE OR REPLACE FUNCTION get_index_columns (p_index_name IN VARCHAR2) RETURN VARCHAR2
IS
  v_column_string VARCHAR2(2000) := '';
BEGIN
     FOR p_record IN (SELECT column_name
                        FROM user_ind_columns
                       WHERE index_name = p_index_name
                    ORDER BY column_position) LOOP
         v_column_string := v_column_string || p_record.column_name || ', ';
     END LOOP;
     v_column_string := substr(v_column_string,1, length(v_column_string)-2);
     RETURN v_column_string;
EXCEPTION
         WHEN OTHERS THEN RETURN '' ;
END;

/* Queries for creating and dropping indexes*/
SELECT 'CREATE INDEX ' || i.index_name || ' ON ' || i.table_name 
   || '(' || get_index_columns(i.index_name) 
   || ') TABLESPACE INDX;' test
  FROM user_indexes i
 WHERE i.table_name LIKE 'PCS_%'
   AND i.uniqueness = 'NONUNIQUE';
SELECT 'CREATE UNIQUE INDEX ' || i.index_name || ' ON ' || i.table_name 
   || '(' || get_index_columns(i.index_name) 
   || ') TABLESPACE INDX;' test
  FROM user_indexes i
 WHERE i.table_name LIKE 'PCS_%'
   AND i.uniqueness = 'UNIQUE';
   
SELECT 'DROP INDEX ' || i.index_name || ';' test
  FROM user_indexes i
 WHERE i.table_name LIKE 'PCS_%'; 

It is generic, so you might have to change some things to get some more specifics into your create index script.
Hope you guys can use it.
Mark