Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Unanswered: Need a way to automate index rebuild statements.

    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)

  2. #2
    Join Date
    Mar 2008
    Posts
    2

    Bump it up

    Wanted to bump this up to the top.

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    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.
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Have a look at sp__revindex
    The procs can be downloaded from http://www.edbarlow.com/download/procs.tgz

  5. #5
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Hi,

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •