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.

Sybase : How to select random number within a specific range?

Let’s say, I need a random number within a specific range, how do I do that?

Here’s the code that will do it:

How to trace user activity to a file in SAP Sybase ASE

Today, I’ve been checking if there are any videos on YouTube on Sybase ASE’s dbcc traceon usage and I’ve found this interesting video by Lester Leonares about a nice functionality to log user activity to a file:


(This video is a part of the SAP D&T Academy, definitely worth checking out.)

Googling further on the “set tracefile”, I’ve found a White Paper by Sybase entitled “Application Tracing in Adaptive Server Enterprise (ASE) 15” (opens pdf file in new window).

Below are some notes from the video and the white paper.

To enable tracing:

To disable tracing:

The “set” commands in ASE whose output can be traced under application tracing context are:

Obviously, as you could imagine, there are some documents in Sybase Infocenter about Finding Slow Running Queries where you can read about tracefile and see some examples. This link is an excerpt from a Sybase book on Performance and Tuning Series: Query Processing and Abstract Plans (opens a 3.9MB pdf file in new window).

Sybase APT Workbench – Text GUI RAD

I’m just curious if any of you had/have pleasure to work with Sybase’s text GUI rapid application development environment called APT Workbench.

Below are some screenshots to illustrate what I’m writing about.

First, the login window:
Sybase APT Workbench - Login screen

Creating new form:
Sybase APT Workbench - New Form

Forms menu:
Sybase APT Workbench - Forms Menu

Form attributes:
Sybase APT Workbench - Form Attributes

Fields menu:
Sybase APT Workbench - Fields Menu

Field attributes:
Sybase APT Workbench - Field Attributes

Field special attributes:
Sybase APT Workbench - Field Special Attributes

Does any of the keywords ring the bell?:

After Googling for a while I’ve found some archive of a group: sybase.public.apt-dwb-sqr and also here on Sybase Community Forums.

Since APT isn’t, for quite a long time, any more supported by Sybase, some applications needed to be converted/rewritten to other platforms. I’ve seen some code automatically converted from APT to PowerBuilder by AnyLEX (site unfortunately under construction).

If you happen to be stuck with some of your legacy code written in the APT, contact me, I can try to help.

Writing self explanatory code

Walrus naming variables

Michael Zuskin has posted a nice article about naming conventions:

Elegant Programming: The Art of Naming (Part 1)
— Give all entities mentioned in the code (DB tables, DB tables’ fields, variables, classes, functions, etc.) meaningful, descriptive names that make the code easily understood. The names should be so self-explanatory that it eliminates the need for comments in most cases.
Use the words per and by as often as possible – they really simplify a developer’s life. A variable’s name li_cows_per_farm is better than li_total_cows, and a function name uf_retrieve_city_by_country tells us more than uf_retrieve_city, especially if it doesn’t have parameters that supply the “by what” information…

I really like… not, reading through code where variables are named @var1, @aa, temporary tables #temp, #t1. When I need to touch such code, I have “a bad habit” of renaming those, re-indenting the code, if necessary, in case there are mixed tabs with spaces, unify them, basically leave the code in a little better shape than I’ve welcomed it.

I’m waiting for next part by Michael.

Sybase : How to remove leading zeros from a column?

This is how I do it:

Obviously, the most interesting part for me is the:

construct that finds the position of the first non-ZERO character in the <column> to start substring from.

For detailed info about used functions, please visit Sybase’s documentation:

patindex

char_length