Magento 2 Clear Database

magento2-clear.sql

use magento;

##########################################################
# PRODUCTS
##########################################################
DELETE FROM `catalog_product_bundle_option`;
DELETE FROM `catalog_product_bundle_option_value`;
DELETE FROM `catalog_product_bundle_selection`;
DELETE FROM `catalog_product_entity_datetime`;
DELETE FROM `catalog_product_entity_decimal`;
DELETE FROM `catalog_product_entity_gallery`;
DELETE FROM `catalog_product_entity_int`;
DELETE FROM `catalog_product_entity_media_gallery`;
DELETE FROM `catalog_product_entity_media_gallery_value`;
DELETE FROM `catalog_product_entity_media_gallery_value_to_entity`;
DELETE FROM `catalog_product_entity_media_gallery_value_video`;
DELETE FROM `catalog_product_entity_text`;
DELETE FROM `catalog_product_entity_tier_price`;
DELETE FROM `catalog_product_entity_varchar`;
DELETE FROM `catalog_product_link`;
DELETE FROM `catalog_product_link_attribute_decimal`;
DELETE FROM `catalog_product_link_attribute_int`;
DELETE FROM `catalog_product_link_attribute_varchar`;
DELETE FROM `catalog_product_option`;
DELETE FROM `catalog_product_option_price`;
DELETE FROM `catalog_product_option_title`;
DELETE FROM `catalog_product_option_type_price`;
DELETE FROM `catalog_product_option_type_title`;
DELETE FROM `catalog_product_option_type_value`;
DELETE FROM `catalog_product_super_attribute_label`;
DELETE FROM `catalog_product_super_attribute`;
DELETE FROM `catalog_product_super_link`;
DELETE FROM `catalog_product_website`;
DELETE FROM `catalog_category_product_index`;
DELETE FROM `catalog_category_product`;
DELETE FROM `cataloginventory_stock_item`;
DELETE FROM `cataloginventory_stock_status`;
DELETE FROM `catalog_product_entity`;
DELETE FROM `sequence_product`;
DELETE FROM `sequence_product_bundle_option`;
DELETE FROM `sequence_product_bundle_selection`;

DELETE curpc FROM `catalog_url_rewrite_product_category` curpc
    INNER JOIN url_rewrite ur on curpc.url_rewrite_id = ur.url_rewrite_id
        AND `ur`.`entity_type` = 'product';
DELETE FROM `url_rewrite` WHERE `entity_type` = 'product';

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value_to_entity`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value_video`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `sequence_product`;
TRUNCATE TABLE `sequence_product_bundle_option`;
TRUNCATE TABLE `sequence_product_bundle_selection`;
SET FOREIGN_KEY_CHECKS = 1;


##########################################################
# REVIEWS
##########################################################
DELETE FROM `rating_option_vote`;
DELETE FROM `rating_option_vote_aggregated`;
DELETE FROM `review`;
DELETE FROM `review_detail`;
DELETE FROM `review_entity_summary`;
DELETE FROM `review_store`;

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `rating_option_vote`;
TRUNCATE TABLE `rating_option_vote_aggregated`;
TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_detail`;
TRUNCATE TABLE `review_entity_summary`;
TRUNCATE TABLE `review_store`;
SET FOREIGN_KEY_CHECKS = 1;

##########################################################
# CATEGORIES
##########################################################
DELETE FROM `catalog_category_entity`;
DELETE FROM `catalog_category_entity_datetime`;
DELETE FROM `catalog_category_entity_decimal`;
DELETE FROM `catalog_category_entity_int`;
DELETE FROM `catalog_category_entity_text`;
DELETE FROM `catalog_category_entity_varchar`;
DELETE FROM `catalog_category_product`;
DELETE FROM `catalog_category_product_index`;
DELETE FROM `sequence_catalog_category`;

DELETE FROM `url_rewrite` WHERE `entity_type` = 'category';

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `sequence_catalog_category`;
SET FOREIGN_KEY_CHECKS = 1;

##########################################################
# CUSTOMERS
##########################################################
DELETE FROM `customer_address_entity`;
DELETE FROM `customer_address_entity_datetime`;
DELETE FROM `customer_address_entity_decimal`;
DELETE FROM `customer_address_entity_int`;
DELETE FROM `customer_address_entity_text`;
DELETE FROM `customer_address_entity_varchar`;
DELETE FROM `customer_entity`;
DELETE FROM `customer_entity_datetime`;
DELETE FROM `customer_entity_decimal`;
DELETE FROM `customer_entity_int`;
DELETE FROM `customer_entity_text`;
DELETE FROM `customer_entity_varchar`;

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE `customer_address_entity`;
TRUNCATE `customer_address_entity_datetime`;
TRUNCATE `customer_address_entity_decimal`;
TRUNCATE `customer_address_entity_int`;
TRUNCATE `customer_address_entity_text`;
TRUNCATE `customer_address_entity_varchar`;
TRUNCATE `customer_entity`;
TRUNCATE `customer_entity_datetime`;
TRUNCATE `customer_entity_decimal`;
TRUNCATE `customer_entity_int`;
TRUNCATE `customer_entity_text`;
TRUNCATE `customer_entity_varchar`;
SET FOREIGN_KEY_CHECKS = 1;

##########################################################
# ORDERS
##########################################################

DELETE FROM `sendfriend_log`;
DELETE FROM `report_compared_product_index`;
DELETE FROM `report_event`;
DELETE FROM `report_viewed_product_aggregated_daily`;
DELETE FROM `report_viewed_product_aggregated_monthly`;
DELETE FROM `report_viewed_product_aggregated_yearly`;
DELETE FROM `report_viewed_product_index`;
DELETE FROM `sales_bestsellers_aggregated_daily`;
DELETE FROM `sales_bestsellers_aggregated_monthly`;
DELETE FROM `sales_bestsellers_aggregated_yearly`;
DELETE FROM `sales_creditmemo`;
DELETE FROM `sales_creditmemo_comment`;
DELETE FROM `sales_creditmemo_grid`;
DELETE FROM `sales_creditmemo_item`;
DELETE FROM `sales_invoice`;
DELETE FROM `sales_invoiced_aggregated`;
DELETE FROM `sales_invoiced_aggregated_order`;
DELETE FROM `sales_invoice_comment`;
DELETE FROM `sales_invoice_grid`;
DELETE FROM `sales_invoice_item`;
DELETE FROM `sales_order`;
DELETE FROM `sales_order_address`;
DELETE FROM `sales_order_aggregated_created`;
DELETE FROM `sales_order_aggregated_updated`;
DELETE FROM `sales_order_grid`;
DELETE FROM `sales_order_item`;
DELETE FROM `sales_order_payment`;
DELETE FROM `sales_order_status_history`;
DELETE FROM `sales_order_tax`;
DELETE FROM `sales_order_tax_item`;
DELETE FROM `sales_payment_transaction`;
DELETE FROM `sales_refunded_aggregated`;
DELETE FROM `sales_refunded_aggregated_order`;
DELETE FROM `sales_shipment`;
DELETE FROM `sales_shipment_comment`;
DELETE FROM `sales_shipment_grid`;
DELETE FROM `sales_shipment_item`;
DELETE FROM `sales_shipment_track`;
DELETE FROM `sales_shipping_aggregated`;
DELETE FROM `sales_shipping_aggregated_order`;
DELETE FROM `quote`;
DELETE FROM `quote_address`;
DELETE FROM `quote_address_item`;
DELETE FROM `quote_id_mask`;
DELETE FROM `quote_item`;
DELETE FROM `quote_item_option`;
DELETE FROM `quote_payment`;
DELETE FROM `quote_shipping_rate`;
DELETE FROM `wishlist`;
DELETE FROM `wishlist_item`;
DELETE FROM `wishlist_item_option`;

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `sendfriend_log`;
TRUNCATE TABLE `report_compared_product_index`;
TRUNCATE TABLE `report_event`;
TRUNCATE TABLE `report_viewed_product_aggregated_daily`;
TRUNCATE TABLE `report_viewed_product_aggregated_monthly`;
TRUNCATE TABLE `report_viewed_product_aggregated_yearly`;
TRUNCATE TABLE `report_viewed_product_index`;
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
TRUNCATE TABLE `sales_creditmemo`;
TRUNCATE TABLE `sales_creditmemo_comment`;
TRUNCATE TABLE `sales_creditmemo_grid`;
TRUNCATE TABLE `sales_creditmemo_item`;
TRUNCATE TABLE `sales_invoice`;
TRUNCATE TABLE `sales_invoiced_aggregated`;
TRUNCATE TABLE `sales_invoiced_aggregated_order`;
TRUNCATE TABLE `sales_invoice_comment`;
TRUNCATE TABLE `sales_invoice_grid`;
TRUNCATE TABLE `sales_invoice_item`;
TRUNCATE TABLE `sales_order`;
TRUNCATE TABLE `sales_order_address`;
TRUNCATE TABLE `sales_order_aggregated_created`;
TRUNCATE TABLE `sales_order_aggregated_updated`;
TRUNCATE TABLE `sales_order_grid`;
TRUNCATE TABLE `sales_order_item`;
TRUNCATE TABLE `sales_order_payment`;
TRUNCATE TABLE `sales_order_status_history`;
TRUNCATE TABLE `sales_order_tax`;
TRUNCATE TABLE `sales_order_tax_item`;
TRUNCATE TABLE `sales_payment_transaction`;
TRUNCATE TABLE `sales_refunded_aggregated`;
TRUNCATE TABLE `sales_refunded_aggregated_order`;
TRUNCATE TABLE `sales_shipment`;
TRUNCATE TABLE `sales_shipment_comment`;
TRUNCATE TABLE `sales_shipment_grid`;
TRUNCATE TABLE `sales_shipment_item`;
TRUNCATE TABLE `sales_shipment_track`;
TRUNCATE TABLE `sales_shipping_aggregated`;
TRUNCATE TABLE `sales_shipping_aggregated_order`;
TRUNCATE TABLE `quote`;
TRUNCATE TABLE `quote_address`;
TRUNCATE TABLE `quote_address_item`;
TRUNCATE TABLE `quote_id_mask`;
TRUNCATE TABLE `quote_item`;
TRUNCATE TABLE `quote_item_option`;
TRUNCATE TABLE `quote_payment`;
TRUNCATE TABLE `quote_shipping_rate`;
TRUNCATE TABLE `wishlist`;
TRUNCATE TABLE `wishlist_item`;
TRUNCATE TABLE `wishlist_item_option`;

# Reset indexes (if you want your orders number start back to 1)
TRUNCATE TABLE `sequence_invoice_1`;
TRUNCATE TABLE `sequence_order_1`;
TRUNCATE TABLE `sequence_shipment_1`;
TRUNCATE TABLE `sequence_creditmemo_1`;

SET FOREIGN_KEY_CHECKS = 1;

##########################################################
# SEARCH RESULTS
##########################################################
DELETE FROM `search_query`;

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `search_query`;
SET FOREIGN_KEY_CHECKS = 1;

##########################################################
# COUNTERS
##########################################################
TRUNCATE `eav_entity_store`;


##########################################################
# DELETE ALL non Magento attribute_options
##########################################################
DELETE m2eao, m2eaos, m2eaov
FROM eav_attribute_option m2eao
INNER JOIN eav_attribute m2ea ON m2ea.attribute_id = m2eao.attribute_id
LEFT JOIN eav_attribute_option_swatch m2eaos ON m2eaos.option_id = m2eao.option_id
LEFT JOIN eav_attribute_option_value m2eaov ON m2eaov.option_id = m2eao.option_id
WHERE m2ea.attribute_code IN (
    SELECT a.attribute_code
    FROM eav_attribute as a
    WHERE a.entity_type_id = 4
      -- project specific skips, not all default Magento need to be skipped (e.g. color options can be project specific)
      AND a.attribute_code NOT IN
          ('allow_message', 'allow_open_amount', 'category_ids', 'cost', 'country_of_manufacture', 'created_at',
           'custom_design', 'custom_design_from', 'custom_design_to', 'custom_layout', 'custom_layout_update',
           'default_category_id', 'description', 'email_template', 'gallery', 'gift_message_available',
           'gift_wrapping_available', 'gift_wrapping_price', 'giftcard_amounts', 'giftcard_type', 'group_price',
           'has_options', 'image', 'image_label', 'is_recurring', 'is_redeemable', 'is_returnable', 'lifetime',
           'links_exist', 'links_purchased_separately', 'links_title', 'manufacturer', 'media_gallery',
           'meta_description', 'meta_keyword', 'meta_title', 'minimal_price', 'msrp', 'msrp_display_actual_price_type',
           'msrp_enabled', 'name', 'news_from_date', 'news_to_date', 'old_id', 'open_amount_max', 'open_amount_min',
           'options_container', 'page_layout', 'price', 'price_type', 'price_view', 'quantity_and_stock_status',
           'recurring_profile', 'related_tgtr_position_behavior', 'related_tgtr_position_limit', 'required_options',
           'samples_title', 'shipment_type', 'short_description', 'sku', 'sku_type', 'small_image', 'small_image_label',
           'special_from_date', 'special_price', 'special_to_date', 'status', 'swatch_image', 'tax_class_id',
           'thumbnail', 'thumbnail_label', 'tier_price', 'ts_dimensions_height', 'ts_dimensions_length',
           'ts_dimensions_width', 'updated_at', 'upsell_tgtr_position_behavior', 'upsell_tgtr_position_limit',
           'url_key', 'url_path', 'use_config_allow_message', 'use_config_email_template', 'use_config_is_redeemable',
           'use_config_lifetime', 'visibility', 'weight', 'weight_type', 'language_fallback_locked',
           'tweakwise_related_template', 'tweakwise_upsell_template', 'mct_seo_default_category')
      AND a.attribute_code NOT LIKE '%postnl%'
      AND a.frontend_label NOT LIKE '%Akeneo%'
    ORDER BY a.attribute_code
)


##########################################################
# RE_CREATE ROOT CATEGORY
##########################################################
INSERT INTO `catalog_category_entity` (`entity_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES
(1, 0, 0, '2016-05-09 08:03:52', '2016-05-09 08:03:52', '1', 0, 0, 1),
(2, 3, 1, '2016-05-09 08:03:52', '2016-05-09 08:03:52', '1/2', 1, 1, 0);