How to get index name, key and description from sybase table

Question

I want to create index for specific table from Unix shell script, so that I need index name, index keys, and index description for that particular table.

"sp_helpindexes" gives all these details with some unwanted lines, but I need alternative way to get only index name, key and description.

Anyone please help me on this ....?

Answer 1

Custom made SQL request

You can find description of the sp_helpindex using sp_helptext command like this :

use sybsystemprocs
go

sp_helptext sp_helpindex
go

This will give you the definition of your stored prcedure. Then you can extract your required SQL request (which could be tricky, another store_proc).

Shell script to process output of sp_helpindex

On other side, it seems it is just a shell problem, once you can call your SQL server. For example, using the sqsh program (an isql like program), you can have a file myindexes.sql containing :

use databaname
go
sp_helpindex tablename
go

Then the command

sqsh -U username -P password -S SYBASESERVER -i myindexes.sql -h > myindexes.txt

will give you the sp_helpindex output that you can process. Using the sqsh output, the line 3 contain index name and keys, the line 4 the description. I use :

#!/bin/bash

# Call sqh command : output in myindex.txt
sqsh -U username -P password -S SYBASESERVER -i myindexes.sql -h > myindex.txt

# Then process the output
INAME=`sed '3!d' myindex.txt | tr -s ' ' | cut -d ' ' -f 2`
IKEYS=`sed '3!d' myindex.txt | tr -s ' ' | cut -d ' ' -f 3`
IDESC=`sed '4!d' myindex.txt | tr -s ' ' | cut -f 2`

# print out the values
echo "$INAME $IKEYS $IDESC"

# Clean up the files
rm myindex.txt

Hope this helps a little.

Answer 2

Get index name from sysindexes table
select name,indid,keycnt from sysindexes where id=object_id("table_name")
and then use index_col(table_name,indexid,keycnt-1) to find keys.

hope this will help

Thanks,
Gopal

Answer 3

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';