how to drop all civicrm tables using drush?

Question

Civi is using it's own (not shared with Drupal) database. During a script to rebuild a dev/test site from production data I want to drop all the tables in the civi dev db. How do I do that?

After painstakingly trying civitables=$(drush civicrm-sql-query "show tables" | tr '\n' ',' | sed 's/,$//') && drush civicrm-sql-query "drop table $civitables" I realized I could more simply do drush civicrm-sql-query "drop table like 'civi%'" but both of these are giving me "Query failed" error. Why would these queries fail or what could I inspect to see why the failed? How can all the tables be dropped using drush?

*edits...

The drop table like... is apparently invalid sql sytax. Doh.

And the civitables=$(drush civicrm-sql-query "show tables" | tr '\n' ',' | sed 's/,$//') && drush civicrm-sql-query "drop table $civitables" appears to fail due to Cannot delete or update a parent row: a foreign key constraint fails which apparently needs SET FOREIGN_KEY_CHECKS=0; in order to work but when I do that the query passes (doesn't produce error) but fails to delete any tables.

Answer 1

There's a file in the [civicrm root]/sql folder called civicrm_drop.mysql. You should be able to use that as source sql/civicrm_drop.mysql. It doesn't drop views or the canary table, but you can do that separately.

Answer 2

I haven't seen anything as clean as drush sql-drop (for drupal) yet, but the following works and is more thorough than the civicrm/sql/civicrm_drop.mysql file:

#!/bin/bash
# include this in devize.sh or whatever script is being used to rebuild your dev/test site

echo "SET FOREIGN_KEY_CHECKS=0;" > drop-tables.sql
drush civicrm-sql-query "show tables" | sed -E 's/(.*)/DROP TABLE IF EXISTS `\1`;/g' >> drop-tables.sql
echo "SET FOREIGN_KEY_CHECKS=1;" >> drop-tables.sql
drush civicrm-sql-query "source drop-tables.sql"
sed -i 's/TABLE/VIEW/g' drop-tables.sql
drush civicrm-sql-query "source drop-tables.sql"
rm drop-tables.sql

# then you'd go and do something like:
# drush cvsqlc < production_db.sql
# but that's not exactly relevant to this question