1 2 3 4 5 6 7 8 9 10 11
| select *
from( select null TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, null INDEX_CATALOG, INDEX_SCHEMA, INDEX_NAME, PRIMARY_KEY, UNIQUE_, CLUSTERED, null TYPE, null FILL_FACTOR, INITIAL_SIZE, 2 NULLS, null SORT_BOOKMARKS, -1 AUTO_UPDATE, 2 NULL_COLLATION, ORDINAL_POSITION, COLUMN_NAME, null COLUMN_GUID, null COLUMN_PROPID, 1 COLLATION, null CARDINALITY, null PAGES, null FILTER_CONDITION, null INTEGRATED
from (select idx.table_owner TABLE_SCHEMA, idx.table_name TABLE_NAME, idx.owner INDEX_SCHEMA, idx.index_name INDEX_NAME, decode(idx.uniqueness, 'UNIQUE', -1, 0) UNIQUE_,decode(idx.index_type, 'IOT - TOP', 1, 0) CLUSTERED,idx.initial_extent INITIAL_SIZE, col.column_position ORDINAL_POSITION, col.column_name COLUMN_NAME
from all_indexes idx, all_ind_columns col
where idx.owner = col.index_owner and idx.index_name = col.index_name and idx.table_owner = col.table_owner and idx.table_name = col.table_name )
left outer join
(select decode(constraint_type, 'P', 1, 0) PRIMARY_KEY, index_name AC_INDEX_NAME
from all_constraints)
on INDEX_NAME = AC_INDEX_NAME) DBSCHEMA_INDEXES
WHERE TABLE_SCHEMA = 'nom_schéma' AND TABLE_NAME = 'nom_table_'
order by 8, 10, 5, 6, 17 |
Partager