My query returns the following output :


INDEX_NAME Schema Name Table Name Column Name

BOOK DEMO5 TBK FBKID
BOOK PRU00N TBK FBKID
BOOK CPB004 TBK FBKID

BOOKTY DEMO5 TBKTYP FBKTYPID
BOOKTY EVG004 TBKTYP FBKTYPID
BOOKTY PIMCO5 TBKTYP FBKTYPID
BOOKTYP PRU00N TBKTYP FBKTYPID

BOOK_FU DEMO4 TBKCON FBKINID
BOOK_FU BOAP04 TBKCON FCONID
BOOK_FU COL004 TBKCON FBOOKID
BOOK_FU DI0004 TBKCON FBOOKID

The BOOK index is in 3 schemas. I want to display it only once for every schema and the other data remains the same.
Similarly for the other indexes also.

I couldn't understand your required result.
(Same about your previous post "Sysindexes & syskeys".)

Please show your required result from the sample data.

Desired Out put : and its Explanation : Indexes exist in different schema's. I want to show distinct indexes from all schemas. In the 3rd index BOOK_FU we see that different columns are used on same index in different schemas. We want to synchronize all schemas so that they contain indexes which are based on same columns and delete the rest. So after getting the result set from the index BOOK_FU we may further decide that the index on the FCONID column is ok and so we remove the others.

Desired Output :

IX_NAME Sch Name Tb Name Col Name

BOOK DEMO5 TBK FBKID
PRU00N
CPB004

BOOKTY DEMO5 TBKTYP FBKTYPID
EVG004
PIMCO5
PRU00N

BOOK_FU DEMO4 TBKCON FBKINID
BOAP04 TBKCON FCONID
COL004 TBKCON FBOOKID

I'm sure you will be able to achieve this using one of the analytical functions, say, ROW_NUMBER(), and a CASE expression.