Answers.Body: <p>I haven't seen anything as clean as <code>drush sql-drop</code> (for drupal) yet, but the following works and is more thorough than the <code>civicrm/sql/civicrm_drop.mysql</code> file:</p>
  
  <pre><code>#!/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;" &gt; drop-tables.sql
  drush civicrm-sql-query "show tables" | sed -E 's/(.*)/DROP TABLE IF EXISTS `\1`;/g' &gt;&gt; drop-tables.sql
  echo "SET FOREIGN_KEY_CHECKS=1;" &gt;&gt; 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 &lt; production_db.sql
  # but that's not exactly relevant to this question
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-12T14:46:53.693Z
Answers.Text: 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'`.
Answers.UserDisplayName: alec
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-11T22:09:45.563Z
Answers.LastActivityDate: 2020-03-11T22:09:45.563Z
Answers.UserDisplayName: alec
Answers.Body: <p>There's a file in the [civicrm root]/sql folder called civicrm_drop.mysql. You should be able to use that as <code>source sql/civicrm_drop.mysql</code>. It doesn't drop views or the canary table, but you can do that separately.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-11T21:14:28.553Z
Answers.Text: so like `drush civicrm-sql-query --file=sites/all/modules/civicrm/sql/civicrm_drop.mysql` I presume is what you mean?
Answers.UserDisplayName: alec
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-11T21:22:04.907Z
Answers.Text: 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.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-11T21:24:15.653Z
Answers.Text: do you mean `drush civicrm-sql-query "source sites/all/modules/sql/civicrm_drop.mysql"`?
Answers.UserDisplayName: alec
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-11T21:25:25.980Z
Answers.Text: Right, you'd probably have to give the path from drupal root.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-11T21:46:34.387Z
Answers.Text: Any idea why civi's `civicrm_drop.mysql` doesn't drop tables that are "views"?
Answers.UserDisplayName: alec
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-11T21:55:20.817Z
Answers.Text: Probably just nobody's gone and implemented it.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-03-11T21:03:58.033Z
Answers.LastActivityDate: 2020-03-11T21:03:58.033Z
Answers.UserDisplayName: Demerit
Body: <p>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?</p>
  
  <p>After painstakingly trying <code>civitables=$(drush civicrm-sql-query "show tables" | tr '\n' ',' | sed 's/,$//') &amp;&amp; drush civicrm-sql-query "drop table $civitables"</code> I realized I could more simply do <code>drush civicrm-sql-query "drop table like 'civi%'"</code> 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 <code>drush</code>?</p>
  
  <p>*edits...</p>
  
  <p>The <code>drop table like...</code> is apparently invalid sql sytax.  Doh.</p>
  
  <p>And the <code>civitables=$(drush civicrm-sql-query "show tables" | tr '\n' ',' | sed 's/,$//') &amp;&amp; drush civicrm-sql-query "drop table $civitables"</code> appears to fail due to <code>Cannot delete or update a parent row: a foreign key constraint fails</code> which apparently needs <code>SET FOREIGN_KEY_CHECKS=0;</code> in order to work but when I do that the query passes (doesn't produce error) but fails to delete any tables.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-03-11T21:09:02.343Z
Comments.Text: Drupal has `drush sql-drop` and that's what I want for civicrm!
Comments.UserDisplayName: alec
Tags: <mysql><drush><cli>
Title: how to drop all civicrm tables using drush?
ThreadText: # how to drop all civicrm tables using drush?
  
  (Asked by: username_0 on 2020-03-11)
  
  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.
  
  **Tags:** mysql, drush, cli
  
  > **Comments:**
  > **username_0**: Drupal has `drush sql-drop` and that's what I want for civicrm!  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2020-03-11. Score: 1
  
  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
  
  ```
  
  > **Comments:**
  > **username_0**: 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'`.  
  
  ---
  
  ### Answer by username_1 on 2020-03-11. Score: 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.
  
  > **Comments:**
  > **username_0**: so like `drush civicrm-sql-query --file=sites/all/modules/civicrm/sql/civicrm_drop.mysql` I presume is what you mean?  
  > **username_1**: 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.  
  > **username_0**: do you mean `drush civicrm-sql-query "source sites/all/modules/sql/civicrm_drop.mysql"`?  
  > **username_1**: Right, you'd probably have to give the path from drupal root.  
  > **username_0**: Any idea why civi's `civicrm_drop.mysql` doesn't drop tables that are "views"?  
  > **username_1**: Probably just nobody's gone and implemented it.  
  
  ---
  
  