Sybase: Get info regarding indexes and which segment they are located on

The following SQL statement displays all indexes on user tables in the current database, with the related table, whether it’s a clustered index and on which segment it is located:

select so.name as TableName, si.name as IndexName, ss.name as SegmentName, case when (si.status & 16 = 16 OR si.status2 & 512 = 512) then ‘clustered’ else ‘non-clustered’ end as ‘Clustered’ from sysindexes si, syssegments ss, sysobjects so where si.segment=ss.segment and si.id=so.id and so.type=’U’ and si.name != so.name order by so.name, si.name, ss.name

Please note that clustered indexes are usually located on the default segment.

Combine with the output of sp_estspace, you can find out what the required size of the index segment should be.

Leave a Reply

Your email address will not be published.