| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: can I do this in SQL??
I tidied up this guys query for you, now it uses the tables you want (user_
... not all_...) so you don't have to worry about the owner stuff. It is
quite a nice query:
select substr(a.index_name,1,30) index_name,
decode(a.uniqueness,'UNIQUE','Yes','No') uniqueness,
max(decode( b.column_position, 1, substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 2, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 3, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 4, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 5, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 6, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 7, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 8, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 9, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 10, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 11, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 12, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 13, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 14, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 15, ', '||substr(b.column_name,1,30), NULL ))
||
max(decode( b.column_position, 16, ', '||substr(b.column_name,1,30),
NULL ))cols
from user_indexes a, user_ind_columns b
where a.table_name = 'MYTABLE'
and a.table_name = b.table_name
and a.index_name = b.index_name
group by substr(a.index_name,1,30), a.uniqueness
;
Received on Fri Oct 22 1999 - 08:42:54 CDT
 |
 |