8

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

15

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.

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

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

1

@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 '18 at 10:54
-2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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