Scripts to list all the indexes in the schema
Posted by decipherinfosys on September 18, 2008
In one of our previous blog post, we posted the script to disable & enable constraints and triggers in Oracle. We have put similar scripts for sqlserver as well.
In this blog post, we are providing a small script to display all the indexes in the specific schema.
/*
– This script will produce a report (indexlist.lst) of all the indexes
– of the schema in which it is run.
–
*/
column tname format A20 heading ‘Table Name:’ trunc
column iname format A20 heading ‘Index Name:’ trunc
column cname format A30 heading ‘Column Name:’ trunc
break on tname on iname
spool indexlist.lst
select substr(table_name,1,20) tname,
substr(index_name,1,20) iname,
substr(column_name,1,30) cname
from user_ind_columns
order by table_name, index_name,column_position;
spool off
exit
As mentioned, this will create the spool file in the directory from which it is run. It will help us determine any redundant and/or duplicate indexes. We can further enhance the query to include the status of the index and also when was the last time statistics was collected on the index.
select substr(ui.table_name,1,20) tname,
substr(ui.index_name,1,20) iname,
substr(column_name,1,30) cname,
ui.status, ui.last_analyzed
from user_ind_columns uic
inner join user_indexes ui
on ui.table_name = uic.table_name
and ui.index_name = uic.index_name
order by ui.table_name, ui.index_name,column_position;
Tname said
Cool!