Sybase : How to check programmatically if index contains particular column?

So you’d like to check if a certain column is included in an index created on a table, that’s easy:

unless you want to do that in a programmatic way, because parsing sp_helpindex output is out of question and you’d like to perform some actions depending on the result (eg. drop & recreate the index with the missing column).

The first and most obvious idea was to check if this information is in the sysindexes table, so, let’s check:

unfortunately, there is no name of the column included in this table:

Okay, so if the information is not there, how does sp_helpindex know what to display? Let’s check by looking at the sp_helpindex source code:

Bingo! The answer is hidden in this line:

Let’s check what the documentation says about index_col:

index_col

Description

Returns the name of the indexed column in the specified table or view.

Syntax

index_col (object_name, index_id, key_# [, user_id])
Parameters

object_name
is the name of a table or view. The name can be fully qualified (that is, it can include the database and owner name). It must be enclosed in quotes.

index_id
is the number of object_name’s index. This number is the same as the value of sysindexes.indid.

key_#
is a key in the index. This value is between 1 and sysindexes.keycnt for a clustered index and between 1 and sysindexes.keycnt+1 for a nonclustered index.

user_id
is the owner of object_name. If you do not specify user_id, it defaults to the caller’s user ID.

Examples

Example 1

Finds the names of the keys in the clustered index on table t4:

Pretty neat! But for my use case, not good enough, I don’t want to run a while loop every time I’d like to check that, so how to get rid of the loop?

Let’s make use of set based approach. I’m going to project a list of 31 integers from 1 to 31 (31 is the maximum amount of columns that a composite index can contain) that are available and waiting for you in master..spt_values table:

That’s it for today.

Sybase : How to disable return status when executing stored procedure?

Let’s say I want to run a stored procedure in an isql session and then send the result set via mail:

but then you get this “(return status = 0) which is not that nice. So for some time I’ve been using grep -v “return status” or sed on the result file to remove the unwanted line until I’ve found out that you can actually disable printing of the return status with a nice command:

Here’s the result set with this parameter turned off:

Quoting the documentation:

proc_return_status
controls sending of a return status TDS token back to the client. set proc_return_status off suppresses sending the return status token to the client, and isql client does not display the (return status = 0) message. The default for this parameter is on.