list indexes

[Thorsten3]

I just learned how to create an index and how to drop an index.
Is there a way to list or view indexes?

 

[Tim1]

Hiya,

Depending on which RDBMS you are using, there is a system table that holds the information.

In Sybase/SQL server, the syntax is

SELECT name FROM sysindexes

This will list ALL indexes in a database.

If you want to restrict it to one table use :

SELECT name FROM sysindexes
WHERE id=OBJECT_ID('table_name')

HTH

Tim

 

[crufty]

In Oracle:

select * from ind;

This will list all indeces and a ton of information about each index.

 

[Thorsten3]

Thanks a lot!
Unfortunatly I use DB2 and both comands didn't work.

 

[karluk]

It's been a long time since I've worked with DB2, but as I recall the catalog table is sysibm.sysindexes. Please check if that works.

If not you may want to post your question in forum178, which is specific to DB2.

 

[PruSQLer]

Good recall Karluk! The table is indeed sysibm.sysindexes.

 

[Thorsten3]

Thanks, it worked!

describe select * from sysibm.sysindexes
select NAME,CREATOR, TBNAME, COLNAMES from sysibm.sysindexes

sorry I didn't know about the DB2 forum