Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


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

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


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

<span>%d</span> bloggers like this: