Skip to Content

How to drop and recreate a primary index ?

Dear Experts,

we have a NetWeaver 7.4 system, running on Sybase ASE 15.something database.

We have a few corrupt tables, that need to be fixed. On 2 tables, the primary index is corrupt. Hence it's necessary to drop it and to recreate it. Unfortunately, this can't be done in SE14. Hence, please kindly advise on how to do that from isql.

How do I get the correct DDL to recreate the primary index ? Many thanks for your help!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 06, 2017 at 06:03 PM

    You could use the "ddlgen" utility that comes with ASE.
    Parameters would be:
    -U: login
    -P: password
    -D: database name
    -T: 'U' for "user table"
    -N: name of the table
    -S: hostname and port number of the ASE

    ddlgen will output a script for creating the table and it's indexes Don't run the entire script, as it drops and recreates the table! Just extract the index DDL from that script.

    Example:
    [bret@myhost rel16]$ isql -Usa
    Password:
    1> use test
    2> go
    1> create table mytable (c1 int)
    2> go
    1> create unique nonclustered index myindex on mytable (c1)
    2> go
    1> exit
    [bret@myhost rel16]$ ddlgen -Usa -Pmypassword -Dtest -TU -Nmytable -Smyhost:4220

    -----------------------------------------------------------------------------
    -- DDL for Table 'test.dbo.mytable'
    -----------------------------------------------------------------------------
    print '<<<<< CREATING Table - "test.dbo.mytable" >>>>>'
    go

    use test
    go

    setuser 'dbo'
    go

    IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid AND o.name = 'mytable' AND u.name = 'dbo' AND o.type = 'U')
    drop table mytable

    IF (@@error != 0)
    BEGIN
    PRINT 'Error CREATING table "test.dbo.mytable"'
    SELECT syb_quit()
    END
    go

    create table mytable (
    c1 int not null
    )
    lock allpages
    with dml_logging = full
    on 'default'
    go


    setuser
    go

    -----------------------------------------------------------------------------
    -- DDL for Index 'myindex'
    -----------------------------------------------------------------------------

    print '<<<<< CREATING Index - "myindex" >>>>>'
    go

    create unique nonclustered index myindex
    on test.dbo.mytable(c1)
    with index_compression = none
    go

    Add comment
    10|10000 characters needed characters exceeded

  • May 07, 2017 at 01:43 PM

    If by 'primary index' you're referring to an index associated with a primary key constraint, then you'll need to issue 2x alter table commands ... one to drop the current PK constraint ... one to recreate the PK constraint.

    If in doubt as to what to do, run the ddlgen command recommended by Bret and post the complete output back here to this thread (preference would be to place the output in a *.txt file and attach said file to your response).

    Add comment
    10|10000 characters needed characters exceeded