Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    13

    Unanswered: problem in SQL script to drop tables

    Hi,

    How can we write a SQL statement to drop a table. The drop statement should not generate any error if the table doesn't exists. I have achieved the same in Oracle by catching the 'table not found exception'

    The code I wrote is as follows:

    CREATE PROCEDURE drop_table(pTableName VARCHAR2)
    IS
    vCommand VARCHAR2(1024);
    table_not_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_not_exist,-942);
    BEGIN
    vCommand := 'DROP TABLE ' || pTableName || ' CASCADE CONSTRAINTS';
    EXECUTE IMMEDIATE vCommand;
    EXCEPTION
    WHEN table_not_exist THEN
    NULL;
    WHEN OTHERS THEN
    RAISE;
    END drop_table;

    Can I do something equivalent in Sybase?
    Thanks,
    Siddharth

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Sybase doesn't require you to jump through hoops and "catch" the error code. Seems kludgy. In T-SQL, you can do something like this:

    Code:
    if exists( SELECT * 
                 FROM sysobjects 
                WHERE ID = object_id( 'table_name' ) ) 
    	DROP TABLE table_name
    I think the object_id is indexed in sysobjects whereas the name is not, although I may be wrong (runs off to check the sp_help sysobjects output ).

    Ok, I see there's (at least in 12.5) an index upon id and a composite index upon name, uid. So either query will work fine.

    edit: I see you found the answer in MS SQL Forums as well.

    Thanks,

    Matt

  3. #3
    Join Date
    Jan 2002
    Posts
    13
    Hi,
    Sorry to be posting this on this forum, but I would just like to know if such a statement is possible in Oracle and DB2 also?

    i.e. statement of the type

    if exists( SELECT *
    FROM sysobjects
    WHERE ID = object_id( 'table_name' ) )
    DROP TABLE table_name

    Thanks and regards,
    Siddharth.

Posting Permissions

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