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 MSSQL?
    Thanks,
    Siddharth

  2. #2
    Join Date
    Jan 2002
    Location
    Sweden
    Posts
    11

    Arrow

    This is how I do it in T-SQL.

    if exists(select * from sysobjects where name = '<table name>')
    drop table <table name>

  3. #3
    Join Date
    Feb 2002
    Location
    Denmark
    Posts
    4
    Hi

    here is another way in T-SQL that I usually use, because the method abbe supposed I ran into some problem with when creating triggers. The exists returned false even though I could see that the trigger existed.

    When using this method I always got the correct result.

    IF OBJECTPROPERTY(
    object_id(N'['tableowner].['tablename']'),N'IsTable') = 1
    DROP TABLE ['tableowner].['tablename']
    GO

    The OBJECTPROPERTY routine tells you information about an object. in this case if it is a table it will return 1, if not then 0 and NULL if it is not an object.

    sincerily
    Sniskeren

Posting Permissions

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