select index_name, rtrim(xmlagg(xmlelement(c,column_name || ',')).extract ('//text()'), ',') columns
from all_ind_columns
where table_name = 'F0101' and owner = 'TESTDTA' group by index_name
The above is going to show you all the columns in the indexes for F0101. The rows are going to look like:
F0101_7
ABAC01,ABDC,ABAC03
F0101_8
ABAC01,ABDC,ABAC02
F0101_9
ABAC01,ABDC
Which is very cool
But I only want the definition of the unique indexes for the tables that I list, so that SQL gets a little more bespoke:
select t1.index_name, rtrim(xmlagg(xmlelement(c,t1.column_name || ',')).extract ('//text()'), ',') columns
from all_ind_columns t1, all_indexes t2
where t1.table_name = 'F0101'
and t1.table_owner = 'TESTDTA'
and t2.table_owner = 'TESTDTA'
and t1.index_name = t2.index_name
and t2.uniqueness = 'UNIQUE'
group by t1.index_name
So above is the SQL to show me the unique indexes.
