SQL Drop Table

Using DROP TABLE.

  • Beware of that table contents and definition are both gone after a DROP TABLE.
  • Before you DROP any table, you should first remove (drop) any constraints related to the table.
    MySQL, PostgreSQL:
    DROP TABLE [ IF EXISTS ] table-name;
    Example:
    DROP TABLE IF EXISTS BOOKSTORE;
    MySQL, PostgreSQL, Oracle, SQL server:
    DROP TABLE  table-name;
    Example:
    DROP TABLE BOOKSTORE;
    Syntax for table existence test in SQL server:
    IF (EXISTS (SELECT table_name  FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'TheSchema_name' 
                     AND  TABLE_NAME = 'TheTable_name'))
    BEGIN
        DROP TABLE TheTable_name;
    END;
    Example:
    IF (EXISTS (SELECT table_name  FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'Customer'))
    BEGIN
        DROP TABLE Customer;
    END;
    Syntax for table existence test in Oracle:
    DECLARE 
     var_count int;
    BEGIN
     SELECT count(*) INTO var_count FROM all_tables WHERE table_name = 'TheTable_name';
     IF var_count > 0 THEN
       EXECUTE IMMEDIATE  'DROP TABLE TheTable_name';
       END IF;
    END;
    Example:
    DECLARE 
     var_count int;
    BEGIN
     SELECT count(*) INTO var_count FROM all_tables WHERE table_name = 'CUSTOMER';
     IF var_count > 0 THEN
       EXECUTE IMMEDIATE  'DROP TABLE CUSTOMER';
       END IF;
    END;
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.