Systems Engineering and RDBMS

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;

One Response to “Scripts to list all the indexes in the schema”

  1. Tname said

    Cool!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: