rake db:reset Drop all tables but not database

Question

I need to drop all the tables in my database without dropping the database because the user for this database does not have create database privileges.

What is the best way to drop all the tables but not the actual database?

Also, we use rake db:seed to add some entries into one of the tables so I don't want to use a seed file.

Is it required that you do it through rake task? Why not just generate a migration to drop the tables? — Mike Waldrup, Apr 22 '14 at 18:01
The purpose of dropping all the tables is to allow me to clear the database so I can do a clean setup for testing purposes. Ideally I would use db:reset but my database admin doesn't give me an account that can recreate the database. — Phillip Boushy, Apr 22 '14 at 18:03
It's unclear then if you meant to drop the data in the tables, or the tables themselves. More data please. — Mike Waldrup, Apr 22 '14 at 18:08
Drop (and recreate) all the tables or empty all the tables? Maybe something here can help http://stackoverflow.com/questions/1196172/delete-everything-from-all-tables-in-activerecord — , Apr 22 '14 at 18:09
I'd like to drop and recreate all the tables. I do not want to drop all the rows in all tables. — Phillip Boushy, Apr 22 '14 at 18:16

score 9 · Accepted Answer · answered Apr 24 '14 at 17:32

9

This is the solution I eventually came up with after looking at the Truncate method.

namespace :db do
  desc "Erase all tables"
  task :clear => :environment do
    conn = ActiveRecord::Base.connection
    tables = conn.tables
    tables.each do |table|
      puts "Deleting #{table}"
      conn.drop_table(table)
    end
  end
end

answered Apr 24 '14 at 17:32

Phillip Boushy

445
1
4
14

2

Seems to break if there are foreign keys referencing tables :( – Hut8 Sep 30 '15 at 19:28
This could probably be extended pretty easily to look at the tables that reference other tables first and clear them and then clear the remainder. It'd also be pretty easy to update it to clear individual tables rather than all at once. – Phillip Boushy Nov 25 '15 at 22:07

score 2 · Answer 2 · answered Apr 22 '14 at 20:19

2

for testing, i would suggest using:

rake db:test:prepare

It will re-generate all your tables based on your db/schema.rb

answered Apr 22 '14 at 20:19

manu29.d

1,538
1
11
15

score 1 · Answer 3 · answered Feb 16 '19 at 03:59

To solve for:

PG::DependentObjectsStillExist: ERROR: cannot drop table location_types because other objects depend on it

Force a CASCADE.

namespace :db do
  desc "Erase all tables"
  task :clear => :environment do
    conn = ActiveRecord::Base.connection
    tables = conn.tables
    tables.each do |table|
      puts "Deleting #{table}"
      conn.drop_table(table, force: :cascade)
    end
  end
end

score 0 · Answer 4 · edited May 23 '17 at 12:02

0

You could achieve that with the following rake task (which i found here time ago)

namespace :db do
  desc "Truncate all tables"
  task :truncate => :environment do
    conn = ActiveRecord::Base.connection
    tables = conn.execute("show tables").map { |r| r[0] }
    tables.delete "schema_migrations"
    tables.each { |t| conn.execute("TRUNCATE #{t}") }
  end
end

Edited to add a link to the question i found before:

Delete everything from all tables (in Activerecord)

I don't understand why you want to drop all tables if the truncate instruction also make a fresh start of your table.

Anyway, maybe this answers could be helpful too:

https://stackoverflow.com/a/2789515/1639291

https://stackoverflow.com/a/1197218/1639291

edited May 23 '17 at 12:02

Community

1
1

answered Apr 22 '14 at 18:12

JGutierrezC

4,398
5
25
42

Does this actually drop each table or just drop all the rows in each table? – Phillip Boushy Apr 22 '14 at 18:16
Only truncate tables. Isn't it what you want? :| – JGutierrezC Apr 22 '14 at 18:18
I need to drop all tables but leave the database. I will then run rake:migrate to recreate all the tables. – Phillip Boushy Apr 22 '14 at 18:23
I'm receiving an error `ActiveRecord::StatementInvalid: PG::UndefinedObject: ERROR: unrecognized configuration parameter "tables" : show tables` that points to "show tables"... I should probably mention that I'm using PostgreSQL for my production database and sqlite for dev. – Phillip Boushy Apr 22 '14 at 18:33
ah! That's for MySql, for postgres you could use `SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'` instead of `show tables` – JGutierrezC Apr 22 '14 at 19:45

score 0 · Answer 5 · edited May 23 '17 at 12:17

0

If you run rake -P | grep db you will see all the database tasks built into Rails, but none of them seem to do what you're looking for. I believe the only way to do it is using a migration. This answer shows you how.

edited May 23 '17 at 12:17

Community

1
1

answered Apr 22 '14 at 18:22

davidinjc

61
3

rake db:reset Drop all tables but not database

5 Answers5