Sybase: Find all tables in a database with a unique index

The following statement will return a list of all user tables in the current database which have a unique index:

SELECT o.name FROM sysobjects o, sysindexes i WHERE o.id = i.id AND o.type=’U’ AND i.status & 2 = 2 GROUP BY o.name ORDER BY o.name

If you’re also interested in the index name, use the following:
SELECT o.name, i.name FROM sysobjects o, sysindexes i WHERE o.id = i.id AND o.type=’U’ AND i.status & 2 = 2 ORDER BY o.name

One thought on “Sybase: Find all tables in a database with a unique index

  1. You are an absolute life saver. I’m trying to get a sql which will list the largest tables, indexes on a database(600+tables) to enable some reorg because I feel that there is a lot of inefficiencies, table and indexes that need to be fixed.

Leave a Reply

Your email address will not be published.