I use postgresql and I need to drop all the tables which are present in the schema. How can I delete from command prompt.
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 $$;
WHERE schemaname = current_schema() if you want to drop all of the tables in a different schema.
yourdb=# DROP SCHEMA *schema_name* CASCADE;
(works, just tested with Postgresql 9.6.3, might not work with previous releases)
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.
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')