NOTE: This is an old article I wrote in January 2007, it’s still relevant today. It was originally posted on luckydonkey.com which I am in the process of retiring.
There were a number of insightful comments in the original article that I have not copied across. They ended with The Ultimate DropEverything script from Mike Bayer that is now in the SqlAlchemy wiki
I’ve been working in deployment scripts for my current project and sometimes I need to drop all the tables and sequences from the database so I can create everything from scratch. I had been doing a
1 2 3
This was great but a little destructive and over the top. It removes stored procedures and triggers as well which isn’t what I want. So I looked at the SQLAlchemy docs and there is a metadata command drop_all()
Unfortunately this doesn’t CASCADE so this isn’t going to work in cases where tables have foreign keys (which is most of the time). It also leaves sequences in place.
So I looked at dropping each table in turn with a cascade. So how do you get a list of tables and sequences in the database? I could hard code the names of the tables and sequences but that seemed a little poor.
It turns out that PostgreSQL has a set of views that expose the inner workings of your database and a few queries can give you all the information you need.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
The information_schema is full of interesting information. It’s actually a ‘view’ of lower level database tables. You can find all sorts of performance and configuration information in there. Quite handy Here are some docs.