CODEWITHSUNDEEP
sqlenvironment-variablessybaseindexing
MatBailie
MatBailie

Reputation: 86735

Finding Indexes For A Table in Sybase

I am currently working in an environment where I am made double blind.

  1. I have to email any queries I need to run to a employee of the client
  2. That employee runs them in a SAS client, with an odbc connection to SyBase

I need to find out how to ascertain exactly what indexes exist on a specific table. I would use sp_helpindex, but apparently it doesn't exist on their instance of SyBase.

We believe that it is SyBase 12, but again I can't be certain of anything.

Does anyone have SQL for...
- Confirming exactly what version of SyBase we're working on?
- The details of all indexes that exist for a given table?

Upvotes: 1

Views: 27283

Answers (6)

Vishwas
Vishwas

Reputation: 1

select * from sys.sysindexes where tname='Your Table name'

Try the above code, it worked for me.

Upvotes: 0

Orhan Celik
Orhan Celik

Reputation: 1575

For sybase version: 

select @@version

in Sybase version SAP IQ/16, you can get list of indexes with following (table name my_table is case sensitive:

select *
from sys.sysindexes
where tname = 'my_table';

Upvotes: 0

Guna Vamshi
Guna Vamshi

Reputation: 1

Find Indexes on multiple tables, not like image indexes

select name = o.name,iname = i.name from sysindexes i, sysobjects o where o.name in ('table1','table2','table3') and i.name not like 't%' and i.indid >=1 and o.id = i.id

Upvotes: 0

Guna Vamshi
Guna Vamshi

Reputation: 1

You can use this query:

select i.name 
from sysindexes i, sysobjects o
where o.name = 'table_name' and i.indid >=1
and o.id = i.id

Upvotes: 0

Mike Kelley
Mike Kelley

Reputation: 41

Most Sybase products have a -v command line argument to tell the version with or without the engine running.

To find the indexes on any table enter the following, where my_table is your table's name:

select i.name 
from sysindexes i, sysobjects o
where o.name = 'my_table'
  and o.id = i.id

Upvotes: 3

aF.
aF.

Reputation: 66697

To "Confirming exactly what version of SyBase we're working on?"

Why not use:

select @@version

Sybase website is down (at least here), but it would be something like:

IQ SHOW INDEXSET INDEXES

or

IQ SHOW INDEXSET FOR indexset

where indexset is tablename because every table has a indexset assigned with the same name.

If you have access to sybase website possibly you can go further :)

Upvotes: 1

Related Questions