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 as TableName, as IndexName, 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 and so.type=’U’ and != order by,,

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. Required fields are marked *