I use postgresql and I need to drop all the tables which are present in the schema. How can I delete from command prompt.


4 Answers 4


You want to use the CASCADE option of DROP SCHEMA. From the documentation:

CASCADE - Automatically drop objects (tables, functions, etc.) that are contained in the schema, and in turn all objects that depend on those objects

BE CAREFUL - emphasis above mine.

Obviously you'll need to recreate the schema afterwards.

To just drop all tables in the current schema, you can use this script:

    tabname RECORD;
    FOR tabname IN (SELECT tablename 
                    FROM pg_tables 
                    WHERE schemaname = current_schema()) 
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(tabname.tablename) || ' CASCADE';
END $$;

Change WHERE schemaname = current_schema() if you want to drop all of the tables in a different schema.

  • when i use DROP SCHEMA schemaname CASCADE; am getting error ERROR: syntax error at or near "DROP" LINE 2: DROP Schema Oct 17, 2016 at 10:13
  • 3
    LINE 2? What's on line 1?
    – Philᵀᴹ
    Oct 17, 2016 at 10:15
  • actually i need to drop all the tables and its dependent object. but it should not delete procedure and all.. Oct 17, 2016 at 10:27
  • but when i use the drop schema command it delete the schema also. in my case schema should be exists Oct 17, 2016 at 10:28
  • @asalthangam answer updated
    – Philᵀᴹ
    Oct 17, 2016 at 10:36
yourdb=# DROP SCHEMA *schema_name* CASCADE;

(works, just tested with Postgresql 9.6.3, might not work with previous releases)


@Techno Savvy

Drop tables using postgres syntax:

select '-- drop table ' || tablename || ' cascade;' from pg_tables 
where tablename not like 'pg%' and tablename not like 'sql%';

Copy the resulting text into a sql script or psql terminal. Note that the commands are commented out.

  • The question was about dropping tables in a specific schema, I think.
    – mustaccio
    Sep 6, 2018 at 10:54

If you just want a really simple way to do this.. Heres a script I have used in the past

select 'drop table '||table_name||' cascade constraints;' from user_tables;

This will print out a series of drop commands for all tables in the schema. Spool the result of this query and execute it.

Likewise if you want to clear more than tables you can edit the following to suit your needs

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')


  • 3
    The question is Postgres, not Oracle.
    – Philᵀᴹ
    Oct 17, 2016 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.