Hi Everyone,

I am looking for a way to automate a script building process to rebuild
indexes in SYBASE. I have a database with hundreds of indexes I need to rebuild.
Currently I have been creating the drop, and create index statements manually.
Does anyone have an example of a script I can use to build these statements from sysindex or
another system table in SYBASE.

I have included some examples of the scripts I use below. I am using SYBASE version 12.



drop index policy_claim_xref.policy_claim_xref_pk
drop index imbf_request.imbf_request_x02
drop index imbf_request.imbf_request_x03
drop index imbf_request.imbf_request_x03
drop index imbf_request.imbf_request_x05



create unique clustered index policy_claim_xref_pk
on dm0101d.dbo.policy_claim_xref ( policy_number, claim_number)
on 'dmsimg_data_seg1'

create nonclustered index imbf_request_x02
on dm0101d.dbo.imbf_request ( request_date)
create nonclustered index imbf_request_x03
on dm0101d.dbo.imbf_request ( complete_date)
create unique nonclustered index imbf_request_x04
on dm0101d.dbo.imbf_request ( folder_number)
create nonclustered index imbf_request_x05
on dm0101d.dbo.imbf_request ( status_code)

Wanted to bump this up to the top.

I think that requiers some serious digging in systemtables. Starting with sysobjects for the name of the index, joining it with other tables to find the type (clustered/nonclustered) and the columns.
It's a challenge, but a time-consuming one.

Have a look at sp__revindex
The procs can be downloaded from http://www.edbarlow.com/download/procs.tgz

Here i have one point

when you are creating a clustered index means non-clustered indexes will automatically rebuild. no need to drop and create of non-clustered indexes.

So your work will be reduced to 1/3 now.

select "sp_rebuildindex "+name from sysindexes where indid=1


Regards,
Naveen.