Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    5

    Question Unanswered: How to drop table dynamic in sybase?

    I want write a procedure that i can drop all table in a database.
    Using below SQL I can query out all table's name that in a database.
    SELECT name FROM sysobjects WHERE type = 'U'

    then I test below SQL:
    declare @tablename varchar(32)
    select @tablename = "table1"
    drop table @tablename

    In SQL Advantage I get this error:
    Server Message: Number 102, Severity 15
    Line 3:
    Incorrect syntax near '@tablename'.

    So I don't know how to do use sybse procedure ?

    But use Java I can drop table like this:
    ResultSet rs = st.executeQuery(SELECT name FROM sysobjects WHERE type = 'U');
    while (rs.next()) {
    st.execute("drop table " + rs.getString(1));
    }


    please help me , thanks !

  2. #2
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Thumbs up

    Try this:

    declare @tablename varchar(30)
    select @tablename = "table1"
    exec ( "drop table "+@tablename )

    And the store procedure is something like this:


    IF EXISTS( SELECT *
    FROM sysobjects
    WHERE name='sp_tables'
    AND type='P'
    AND uid = user_id() )
    DROP PROCEDURE sp_tables
    go

    CREATE PROCEDURE sp_tables
    AS

    -- Declare the necessary variables
    DECLARE @table VARCHAR(30)

    BEGIN

    DECLARE tables_cursor CURSOR FOR
    SELECT user_name(uid)+"."+name
    FROM sysobjects
    WHERE type = "U"

    -- OPEN CURSOR
    OPEN tables_cursor

    -- FETCH FIRST TABLE
    FETCH tables_cursor INTO @table

    -- @@sqlstatus = 0 means successful fetch
    -- @@sqlstatus = 1 means error on previous fetch
    -- @@sqlstatus = 2 means end of result set reached
    WHILE (@@sqlstatus != 2)
    BEGIN
    -- CHECK FOR ERRORS
    IF (@@sqlstatus = 1)
    BEGIN
    -- ERROR: Cursor de Sistemas - No se pudo Leer
    PRINT "ERROR: Cant read the cursor tables_cursor"
    RETURN -1
    END

    -- DO SOMETHING
    EXEC ( "select * from "+@table )
    -- EXEC ( "DROP TABLE "+@table )

    -- FETCH NEXT TABLE
    FETCH tables_cursor INTO @table
    END

    -- CLOSE CURSOR
    CLOSE tables_cursor

    END
    go




    Bye bye

    Sebastian Isaac Velasco

  3. #3
    Join Date
    Nov 2003
    Posts
    10

    dynamic sql

    Try using sp_remotesql, I guess, you need to enable few things.

    HTH.

Posting Permissions

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