Drop Magento Database from query

The code below does as the title suggests. If anyone has been trying to remove the Magento database for whatever reason you’ll find that with all of the key constraints that come with Magento droping every table at once requires a special bit of code.

Below is from a 1.7.0.2 store, different version of Magento may have slightly different tables and custom modules for Magento will also sometimes add their own tables. But in general the code below should work for most and with one execution drop every table in the db.

SET foreign_key_checks = 0;
DROP TABLE `adminnotification_inbox`, `admin_assert`, `admin_role`, `admin_rule`, `admin_user`, `api2_acl_attribute`, `api2_acl_role`, `api2_acl_rule`, `api2_acl_user`, `api_assert`, `api_role`, `api_rule`, `api_session`, `api_user`, `captcha_log`, `cataloginventory_stock`, `cataloginventory_stock_item`, `cataloginventory_stock_status`, `cataloginventory_stock_status_idx`, `cataloginventory_stock_status_tmp`, `catalogrule`, `catalogrule_affected_product`, `catalogrule_customer_group`, `catalogrule_group_website`, `catalogrule_product`, `catalogrule_product_price`, `catalogrule_website`, `catalogsearch_fulltext`, `catalogsearch_query`, `catalogsearch_result`, `catalog_category_anc_categs_index_idx`, `catalog_category_anc_categs_index_tmp`, `catalog_category_anc_products_index_idx`, `catalog_category_anc_products_index_tmp`, `catalog_category_entity`, `catalog_category_entity_datetime`, `catalog_category_entity_decimal`, `catalog_category_entity_int`, `catalog_category_entity_text`, `catalog_category_entity_varchar`, `catalog_category_product`, `catalog_category_product_index`, `catalog_category_product_index_enbl_idx`, `catalog_category_product_index_enbl_tmp`, `catalog_category_product_index_idx`, `catalog_category_product_index_tmp`, `catalog_compare_item`, `catalog_eav_attribute`, `catalog_product_bundle_option`, `catalog_product_bundle_option_value`, `catalog_product_bundle_price_index`, `catalog_product_bundle_selection`, `catalog_product_bundle_selection_price`, `catalog_product_bundle_stock_index`, `catalog_product_enabled_index`, `catalog_product_entity`, `catalog_product_entity_datetime`, `catalog_product_entity_decimal`, `catalog_product_entity_gallery`, `catalog_product_entity_group_price`, `catalog_product_entity_int`, `catalog_product_entity_media_gallery`, `catalog_product_entity_media_gallery_value`, `catalog_product_entity_text`, `catalog_product_entity_tier_price`, `catalog_product_entity_varchar`, `catalog_product_index_eav`, `catalog_product_index_eav_decimal`, `catalog_product_index_eav_decimal_idx`, `catalog_product_index_eav_decimal_tmp`, `catalog_product_index_eav_idx`, `catalog_product_index_eav_tmp`, `catalog_product_index_group_price`, `catalog_product_index_price`, `catalog_product_index_price_bundle_idx`, `catalog_product_index_price_bundle_opt_idx`, `catalog_product_index_price_bundle_opt_tmp`, `catalog_product_index_price_bundle_sel_idx`, `catalog_product_index_price_bundle_sel_tmp`, `catalog_product_index_price_bundle_tmp`, `catalog_product_index_price_cfg_opt_agr_idx`, `catalog_product_index_price_cfg_opt_agr_tmp`, `catalog_product_index_price_cfg_opt_idx`, `catalog_product_index_price_cfg_opt_tmp`, `catalog_product_index_price_downlod_idx`, `catalog_product_index_price_downlod_tmp`, `catalog_product_index_price_final_idx`, `catalog_product_index_price_final_tmp`, `catalog_product_index_price_idx`, `catalog_product_index_price_opt_agr_idx`, `catalog_product_index_price_opt_agr_tmp`, `catalog_product_index_price_opt_idx`, `catalog_product_index_price_opt_tmp`, `catalog_product_index_price_tmp`, `catalog_product_index_tier_price`, `catalog_product_index_website`, `catalog_product_link`, `catalog_product_link_attribute`, `catalog_product_link_attribute_decimal`, `catalog_product_link_attribute_int`, `catalog_product_link_attribute_varchar`, `catalog_product_link_type`, `catalog_product_option`, `catalog_product_option_price`, `catalog_product_option_title`, `catalog_product_option_type_price`, `catalog_product_option_type_title`, `catalog_product_option_type_value`, `catalog_product_relation`, `catalog_product_super_attribute`, `catalog_product_super_attribute_label`, `catalog_product_super_attribute_pricing`, `catalog_product_super_link`, `catalog_product_website`, `checkout_agreement`, `checkout_agreement_store`, `cms_block`, `cms_block_store`, `cms_page`, `cms_page_store`, `core_cache`, `core_cache_option`, `core_cache_tag`, `core_config_data`, `core_email_template`, `core_flag`, `core_layout_link`, `core_layout_update`, `core_resource`, `core_session`, `core_store`, `core_store_group`, `core_translate`, `core_url_rewrite`, `core_variable`, `core_variable_value`, `core_website`, `coupon_aggregated`, `coupon_aggregated_order`, `coupon_aggregated_updated`, `cron_schedule`, `customer_address_entity`, `customer_address_entity_datetime`, `customer_address_entity_decimal`, `customer_address_entity_int`, `customer_address_entity_text`, `customer_address_entity_varchar`, `customer_eav_attribute`, `customer_eav_attribute_website`, `customer_entity`, `customer_entity_datetime`, `customer_entity_decimal`, `customer_entity_int`, `customer_entity_text`, `customer_entity_varchar`, `customer_form_attribute`, `customer_group`, `dataflow_batch`, `dataflow_batch_export`, `dataflow_batch_import`, `dataflow_import_data`, `dataflow_profile`, `dataflow_profile_history`, `dataflow_session`, `design_change`, `directory_country`, `directory_country_format`, `directory_country_region`, `directory_country_region_name`, `directory_currency_rate`, `downloadable_link`, `downloadable_link_price`, `downloadable_link_purchased`, `downloadable_link_purchased_item`, `downloadable_link_title`, `downloadable_sample`, `downloadable_sample_title`, `eav_attribute`, `eav_attribute_group`, `eav_attribute_label`, `eav_attribute_option`, `eav_attribute_option_value`, `eav_attribute_set`, `eav_entity`, `eav_entity_attribute`, `eav_entity_datetime`, `eav_entity_decimal`, `eav_entity_int`, `eav_entity_store`, `eav_entity_text`, `eav_entity_type`, `eav_entity_varchar`, `eav_form_element`, `eav_form_fieldset`, `eav_form_fieldset_label`, `eav_form_type`, `eav_form_type_entity`, `gift_message`, `googlecheckout_notification`, `importexport_importdata`, `index_event`, `index_process`, `index_process_event`, `log_customer`, `log_quote`, `log_summary`, `log_summary_type`, `log_url`, `log_url_info`, `log_visitor`, `log_visitor_info`, `log_visitor_online`, `newsletter_problem`, `newsletter_queue`, `newsletter_queue_link`, `newsletter_queue_store_link`, `newsletter_subscriber`, `newsletter_template`, `oauth_consumer`, `oauth_nonce`, `oauth_token`, `paypal_cert`, `paypal_payment_transaction`, `paypal_settlement_report`, `paypal_settlement_report_row`, `persistent_session`, `poll`, `poll_answer`, `poll_store`, `poll_vote`, `product_alert_price`, `product_alert_stock`, `rating`, `rating_entity`, `rating_option`, `rating_option_vote`, `rating_option_vote_aggregated`, `rating_store`, `rating_title`, `report_compared_product_index`, `report_event`, `report_event_types`, `report_viewed_product_aggregated_daily`, `report_viewed_product_aggregated_monthly`, `report_viewed_product_aggregated_yearly`, `report_viewed_product_index`, `review`, `review_detail`, `review_entity`, `review_entity_summary`,`review_status`, `review_store`, `salesrule`, `salesrule_coupon`, `salesrule_coupon_usage`, `salesrule_customer`, `salesrule_customer_group`, `salesrule_label`, `salesrule_product_attribute`, `salesrule_website`, `sales_bestsellers_aggregated_daily`, `sales_bestsellers_aggregated_monthly`, `sales_bestsellers_aggregated_yearly`, `sales_billing_agreement`, `sales_billing_agreement_order`, `sales_flat_creditmemo`, `sales_flat_creditmemo_comment`, `sales_flat_creditmemo_grid`, `sales_flat_creditmemo_item`, `sales_flat_invoice`, `sales_flat_invoice_comment`, `sales_flat_invoice_grid`, `sales_flat_invoice_item`, `sales_flat_order`, `sales_flat_order_address`, `sales_flat_order_grid`, `sales_flat_order_item`, `sales_flat_order_payment`, `sales_flat_order_status_history`, `sales_flat_quote`, `sales_flat_quote_address`, `sales_flat_quote_address_item`, `sales_flat_quote_item`, `sales_flat_quote_item_option`, `sales_flat_quote_payment`, `sales_flat_quote_shipping_rate`, `sales_flat_shipment`, `sales_flat_shipment_comment`, `sales_flat_shipment_grid`, `sales_flat_shipment_item`, `sales_flat_shipment_track`, `sales_invoiced_aggregated`, `sales_invoiced_aggregated_order`, `sales_order_aggregated_created`, `sales_order_aggregated_updated`, `sales_order_status`, `sales_order_status_label`, `sales_order_status_state`, `sales_order_tax`, `sales_order_tax_item`, `sales_payment_transaction`, `sales_recurring_profile`, `sales_recurring_profile_order`, `sales_refunded_aggregated`, `sales_refunded_aggregated_order`, `sales_shipping_aggregated`, `sales_shipping_aggregated_order`, `sendfriend_log`, `shipping_tablerate`, `sitemap`, `tag`, `tag_properties`, `tag_relation`, `tag_summary`, `tax_calculation`, `tax_calculation_rate`, `tax_calculation_rate_title`, `tax_calculation_rule`, `tax_class`, `tax_order_aggregated_created`, `tax_order_aggregated_updated`, `weee_discount`, `weee_tax`, `widget`, `widget_instance`, `widget_instance_page`, `widget_instance_page_layout`, `wishlist`, `wishlist_item`, `wishlist_item_option`, `xmlconnect_application`, `xmlconnect_config_data`, `xmlconnect_history`, `xmlconnect_notification_template`, `xmlconnect_queue`;
SET foreign_key_checks = 1;

The most important code in the snippet above is the:

SET foreign_key_checks = 0;

and

SET foreign_key_checks = 1;

These snippets of code disable the key checks and allow the query to execute as intended.