Skip to Content

How to drop and recreate a primary index ?

Apr 27, 2017 at 10:08 AM


avatar image

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Bret Halford
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.

[bret@myhost rel16]$ isql -Usa
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" >>>>>'

use test

setuser 'dbo'

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

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

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


-- DDL for Index 'myindex'

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

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

10 |10000 characters needed characters left characters exceeded
Mark A Parsons 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).

10 |10000 characters needed characters left characters exceeded