CODEWITHSUNDEEP
sqloracle-databaseindexing
André Alves
André Alves

Reputation: 344

Select columns of table index

Oracle has a query that selects the existing indexes of a table. For example: 

SELECT * FROM user_indexes WHERE table_name = 'CM_WCEL';

But I need to recreate the index creation statement. How can I get the remaining information like the affected columns, etc?

Upvotes: 0

Views: 3342

Answers (3)

user330315
user330315

Reputation:

To get the complete DDL for each index, use dbms_metadata.get_ddl():

select index_name, dbms_metadata.get_ddl('INDEX', index_name) as ddl
from user_indexes
where table_name = CM_WCEL';

The DDL is returned as a CLOB. Depending on the SQL client you are using you might need some configuration changes to be able to see the complete code. e.g. in SQL*Plus you need something set long 60000 before you run the select statement.

Upvotes: 3

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

As per the creation of table, the below tables will have the requested information. 

SELECT *
FROM user_ind_columns
WHERE table_name = 'CM_WCEL';

or 

SELECT *
FROM dba_ind_columns
WHERE table_name = 'CM_WCEL';

Upvotes: 3

MoneyPot
MoneyPot

Reputation: 23

this should provide needed information:

 select index_name, column_name
 from user_ind_columns
 where table_name = 'CM_WCEL';

Upvotes: 1

Related Questions