Generate DDL to create indexes associated with a table



My OraFAQ Blog

Contact me

This script generates DDL for all the indexes associated with a given table. It captures only the most basic index metadata; it doesn't support partitioning, IOT, etc.

It captures the following index metadata only: Index name, columns & positions, uniqueness, index tablespaces.

This script is meant as a time-saver, not a replacement for more thorough methods.

You can capture the basic DDL for the base table here.

Note: In 9i and above, you can use the PL/SQL supplied package DBMS_METADATA to extract DDL, instead of using scripts like this one.

  my_owner varchar2(30) := UPPER('&owner');
  my_name varchar2(30) := UPPER('&tabname');

	cursor colcur (ind_in varchar2)
    select * from dba_ind_columns
  	where table_owner=my_owner and table_name=my_name
    and index_name=ind_in
    order by column_position;
  cursor indcur is
  	select * from dba_indEXES
   	where table_owner=my_owner and table_name=my_name;
  createstring varchar2(4000);
	for indrow in indcur loop
  	createstring := 'CREATE';
    if indrow.uniqueness = 'UNIQUE' then
    	createstring := createstring || ' UNIQUE';
    end if;
    createstring := createstring || ' INDEX ' || indrow.index_name || ' ON '
    	|| indrow.table_name || ' (';

    for colrow in colcur (indrow.index_name) loop
			createstring := createstring || colrow.column_name || ',';
    end loop; --colcur 
    createstring := rtrim(createstring,',');
    createstring := createstring || ') ';
    createstring := createstring || ' TABLESPACE ' || indrow.tablespace_name || ';';
		dbms_output.put_line ( createstring );
    dbms_output.put_line (' ' );        
  end loop; --indcur 

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...