Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: dropping multiple tables in SYBASE

    Hi,

    I used the below to drop tables having common prefix like 'TEST'

    isql -U <user> -P <password> -S <server> -D <db> <<!
    select 'drop table' +name from db.owner.sysobjects where name like
    'TEST%' and type = 'U'
    go
    !

    Please let me know how you can solve this.

    Thanks in advance.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Looks ok, now get rid of the heading and row count output and wrap it in another isql
    Code:
    isql -U <user> -P <password> -S <server> -D <db> -e  <<!!
    $(
    isql -U <user> -P <password> -S <server> -D <db> -b  <<!
    set nocount on
    select 'drop table '+name+'
    go' from sysobjects where name like 'TEST%' and type='U'
    go
    !
    )
    !!
    Last edited by pdreyer; 03-29-12 at 05:36.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Another option
    Code:
    DECLARE c1 CURSOR FOR select 'drop table '+name from sysobjects where name like 'TEST%' and type='U'
    go
    declare @t varchar(255)
    open c1
    fetch c1 into @t
    while @@fetch_status=0
    begin
      exec (@t)
      fetch c1 into @t
    end
    CLOSE c1
    DEALLOCATE CURSOR c1
    go

Posting Permissions

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