postgres how to drop all the table in a schema through command

Question

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

Answer 1

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:

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

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

Answer 2

yourdb=# DROP SCHEMA *schema_name* CASCADE;

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

Answer 3

@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.

Answer 4

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')

Source:https://forums.oracle.com/forums/thread.jspa?threadID=614090