Dropping all tables in Sybase ASE including those with constraints

Question

What is the best way to completely drop all tables in a database in Sybase ASE without dropping the database? I have been using a script: from this question but I am getting errors trying to drop all tables in a database due to referential integrity.

In MySQL I could use SET FOREIGN_KEY_CHECKS = 0

Is there a way to do this in Sybase ASE or can the script above be extended to loop through the constraints?

Answer 1

First you have to drop constraints:

declare cur cursor 
for 
  select SOR.Name as constraint, SOT.Name as table
  from sysreferences SR
  join sysobjects SOR on SOR.id = SR.constrid 
  join sysconstraints SC on SC.constrid = SR.constrid
  join sysobjects SOT on SOT.id = SC.tableid
go
declare @constraint varchar(500)
declare @table varchar(500)
declare @SQL varchar(500)

open cur
fetch cur into @constraint,@table 
  while (@@sqlstatus = 0)
  begin

    select @SQL = 'alter table '+@table+' drop '+@constraint
    exec( @SQL)
    fetch cur into @constraint,@table 

  end
close cur
deallocate cursor cur

next you can drop tables.