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?


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.

  • Drupal has drush sql-drop and that's what I want for civicrm!
    – alec
    Mar 11, 2020 at 21:09

2 Answers 2


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.

  • so like drush civicrm-sql-query --file=sites/all/modules/civicrm/sql/civicrm_drop.mysql I presume is what you mean?
    – alec
    Mar 11, 2020 at 21:14
  • I've never used drush in that way but yes if --file means use the file as a sql script then yes. Alternatively source is a sql command so drush civicrm-sql-query "source sql/civicrm_drop.mysql" should work.
    – Demerit
    Mar 11, 2020 at 21:22
  • do you mean drush civicrm-sql-query "source sites/all/modules/sql/civicrm_drop.mysql"?
    – alec
    Mar 11, 2020 at 21:24
  • Right, you'd probably have to give the path from drupal root.
    – Demerit
    Mar 11, 2020 at 21:25
  • Any idea why civi's civicrm_drop.mysql doesn't drop tables that are "views"?
    – alec
    Mar 11, 2020 at 21:46

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:

# 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
  • OMG -- if running on a *BSD server the sed commands are different :/ The second sed needs '' added as an additional argument so it'd be sed -i '' 's/TABLE/VIEW/g'.
    – alec
    Mar 12, 2020 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.