Drop all tables in a database in CockroachDB

Question

Is there a simple command that would let me drop all of the tables in a database? I have users/grants set, so I don’t want to drop the database itself, just the tables within it.

Answer 1

CockroachDB doesn't natively support dropping all tables without dropping the database containing them, but you can by running:

cockroach sql --format=csv -e 'SHOW TABLES FROM databasename' \
  | tail -n +3 \
  | xargs -n1 printf 'DROP TABLE databasename."%s";\n' \
  | cockroach sql

If you don't mind also dropping the database, you could just run DROP DATABASE databasename CASCADE

Answer 2

The code above did not work as is for me (on a Mac running Cockroach in Docker listening to port 26257). This is that works for me:

cockroach sql --insecure --host=localhost:26257 --format=csv -e 'SHOW TABLES FROM defaultdb' \
  | tail -n +2 \
  | cut -d ',' -f2 \
  | xargs -n1 printf 'DROP TABLE defaultdb."%s" cascade;\n' \
  | cockroach sql --insecure --host=localhost:26257