Skip site navigation (1) Skip section navigation (2)

Re: drop all tables in db - without dropdb ?!

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Tom Tom <luondor(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: drop all tables in db - without dropdb ?!
Date: 2004-08-24 20:07:45
Message-ID: 1093378065.15248.36.camel@linda (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, 2004-08-19 at 10:16, Tom Tom wrote:
> hi there,
> 
> i would like to drop all tables in a database without
> having to issue individual drop-statements for every
> table.
> is there a command that can do this?
> 
> i can not use the dropdb since i do not have the
> permissions :(

You could do:

  DROP SCHEMA public CASCADE;

if you have permissions for that.  (It would delete functions and so on
as well, not just tables.)


Or you could do it in shell script:

psql -t -d my_dbname -c "SELECT 'DROP TABLE ' || n.nspname || '.' ||
c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN
pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind =
'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)" >/tmp/droptables

psql -d my_dbname -f /tmp/droptables
-- 
Oliver Elphick                                          olly(at)lfix(dot)co(dot)uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "I saw in the night visions, and, behold, one like the 
      Son of man came with the clouds of heaven, and came to
      the Ancient of days, and they brought him near before 
      him. And there was given him dominion, and glory, and 
      a kingdom, that all people, nations, and languages, 
      should serve him; his dominion is an everlasting 
      dominion, which shall not pass away, and his kingdom 
      that which shall not be destroyed."     
                                    Daniel 7:13,14


In response to

pgsql-novice by date

Next:From: operationsengineer1Date: 2004-08-24 20:44:02
Subject: PGSQL and XML
Previous:From: Josh BerkusDate: 2004-08-24 19:54:19
Subject: Re: drop all tables in db - without dropdb ?!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group