Re: Destroying all exisitng tables in database

From: Hans Peter Wiedau <hpw-nl(at)quelltext(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Destroying all exisitng tables in database
Date: 2005-04-19 13:28:05
Message-ID: 20050419132805.GA5860@entoron.wiedau.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Wed, Apr 13, 2005 at 04:20:40PM +0100, Lim, Terrin wrote:
> I'm trying to drop all tables in a database but I can't use DROP dbname
> as I don't have permission rights to do so. I also can't manually delete
> all the tables cause there are about 200 tables. How do i go about this?
> Thanks.

Here is a little PL/pgSQL sniplet, I used to delete all tables from
schema public:

----- snip-snap -----

CREATE OR REPLACE FUNCTION dropit() RETURNS integer AS'
DECLARE
numberoftables integer := 0;
tabletodrop RECORD;
BEGIN
FOR tabletodrop IN SELECT tablename from pg_tables where
schemaname=''public'' LOOP
numberoftables := numberoftables + 1;
RAISE NOTICE ''Droping table %'', tabletodrop.tablename;
EXECUTE ''DROP TABLE public.''
|| tabletodrop.tablename;
END LOOP;
RETURN numberoftables;
END;
' LANGUAGE plpgsql;

----- snip-snap -----

You execute it by "select dropit();" on the psql commandline.

But be warned, it will drop _all_ tables in the used schema.

cu,

Hans Peter

--
///// Quelltext AG -- Professional Software Services
// // Hans Peter Wiedau <hpw-nl(at)quelltext(dot)com>, CEO
// // Ostenhellweg 31, 44135 Dortmund, Germany
// \\/ fon +49 231 9503750, fax +49 231 9503751
////\\ Web http://www.quelltext.com

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Dextra - Gustavo Bartz Guedes 2005-04-19 13:40:45 PAM authentication
Previous Message Wim Bertels 2005-04-19 13:12:58 Re: brute force attacking the password