How to drop and recreate a primary index ?

[symon_braunbaer]

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!

[former_member188958]

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