Magento 2 – Remove Sample Data/Test Data

Sometimes you wish to remove Sample Data or even Test Data before going live with the website. Before moving the website from development to production, we might need to remove all the test data. Because it is very important to clean junk data.

In this tutorial, I will help you to clear the following Magento 2 test data.

This post will help you to clear the following Magento 2 test data.

• Clean all the products
• Clean all the categories
• Clean all the customers
• Clean all the wishlists
• Clean all the reviews
• Clean all the search results
• Clean all the orders and related data (invoice, shipment & creditmemo)
• Reset all increment Ids

Log in to your PHPMyAdmin using root access and run the following SQL queries to clear those test data.

Note: Please take a backup before performing any database operations.

1. All Products and Related Data

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product_index_tmp`;
TRUNCATE TABLE `catalog_compare_item`;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_price_index`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_bundle_selection_price`;
TRUNCATE TABLE `catalog_product_bundle_stock_index`;
TRUNCATE TABLE `catalog_product_entity`;
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_index_eav`;
TRUNCATE TABLE `catalog_product_index_eav_decimal`;
TRUNCATE TABLE `catalog_product_index_eav_decimal_idx`;
TRUNCATE TABLE `catalog_product_index_eav_decimal_tmp`;
TRUNCATE TABLE `catalog_product_index_eav_idx`;
TRUNCATE TABLE `catalog_product_index_eav_tmp`;
TRUNCATE TABLE `catalog_product_index_price`;
TRUNCATE TABLE `catalog_product_index_price_bundle_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_bundle_sel_idx`;
TRUNCATE TABLE `catalog_product_index_price_bundle_sel_tmp`;
TRUNCATE TABLE `catalog_product_index_price_bundle_tmp`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_idx`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_tmp`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_cfg_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_downlod_idx`;
TRUNCATE TABLE `catalog_product_index_price_downlod_tmp`;
TRUNCATE TABLE `catalog_product_index_price_final_idx`;
TRUNCATE TABLE `catalog_product_index_price_final_tmp`;
TRUNCATE TABLE `catalog_product_index_price_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_agr_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_agr_tmp`;
TRUNCATE TABLE `catalog_product_index_price_opt_idx`;
TRUNCATE TABLE `catalog_product_index_price_opt_tmp`;
TRUNCATE TABLE `catalog_product_index_price_tmp`;
TRUNCATE TABLE `catalog_product_index_tier_price`;
TRUNCATE TABLE `catalog_product_index_website`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
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_link_type`;
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_relation`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_url_rewrite_product_category`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock_status_idx`;
TRUNCATE TABLE `cataloginventory_stock_status_tmp`;
TRUNCATE TABLE `downloadable_link`;
TRUNCATE TABLE `downloadable_link_price`;
TRUNCATE TABLE `downloadable_link_purchased`;
TRUNCATE TABLE `downloadable_link_purchased_item`;
TRUNCATE TABLE `downloadable_link_title`;
TRUNCATE TABLE `downloadable_sample`;
TRUNCATE TABLE `downloadable_sample_title`;
TRUNCATE TABLE `product_alert_price`;
TRUNCATE TABLE `product_alert_stock`;
TRUNCATE TABLE `report_compared_product_index`;
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 `sequence_product`;
INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT  INTO `cataloginventory_stock`(`stock_id`,`website_id`,`stock_name`) VALUES (1,0,'Default');
SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM url_rewrite where entity_type = 'product';

DELETE FROM catalog_category_entity WHERE level > 1;

2. All Customer Data

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `customer_entity`;
TRUNCATE TABLE `customer_entity_datetime`;
TRUNCATE TABLE `customer_entity_decimal`;
TRUNCATE TABLE `customer_entity_int`;
TRUNCATE TABLE `customer_entity_text`;
TRUNCATE TABLE `customer_entity_varchar`;
TRUNCATE TABLE `customer_address_entity`;
TRUNCATE TABLE `customer_address_entity_datetime`;
TRUNCATE TABLE `customer_address_entity_decimal`;
TRUNCATE TABLE `customer_address_entity_int`;
TRUNCATE TABLE `customer_address_entity_text`;
TRUNCATE TABLE `customer_address_entity_varchar`;
TRUNCATE TABLE `customer_grid_flat`;
TRUNCATE TABLE `customer_log`;
TRUNCATE TABLE `customer_visitor`;
TRUNCATE TABLE `persistent_session`;
TRUNCATE TABLE `salesrule_customer`;
TRUNCATE TABLE `magento_customerbalance`;
TRUNCATE TABLE `magento_customerbalance_history`;
TRUNCATE TABLE `magento_customercustomattributes_sales_flat_order`;
TRUNCATE TABLE `magento_customercustomattributes_sales_flat_order_address`;
TRUNCATE TABLE `magento_customercustomattributes_sales_flat_quote`;
TRUNCATE TABLE `magento_customercustomattributes_sales_flat_quote_address`;
TRUNCATE TABLE `magento_customersegment_customer`;
TRUNCATE TABLE `magento_customersegment_event`;
TRUNCATE TABLE `magento_customersegment_event`;
TRUNCATE TABLE `magento_customersegment_website`;
TRUNCATE TABLE `magento_targetrule_customersegment`;
TRUNCATE TABLE `salesrule_customer`;
TRUNCATE TABLE `email_catalog`;
TRUNCATE TABLE `email_abandoned_cart`;
TRUNCATE TABLE `email_automation`;
TRUNCATE TABLE `email_campaign`;
TRUNCATE TABLE `email_catalog`;
TRUNCATE TABLE `email_contact`;
TRUNCATE TABLE `email_contact_consent`;
TRUNCATE TABLE `email_failed_auth`;
TRUNCATE TABLE `email_importer`;
TRUNCATE TABLE `email_order`;
TRUNCATE TABLE `email_review`;
TRUNCATE TABLE `email_rules`;
TRUNCATE TABLE `email_wishlist`;
TRUNCATE TABLE `wishlist`;
TRUNCATE TABLE `wishlist_item`;
TRUNCATE TABLE `wishlist_item_option`;<br />
SET FOREIGN_KEY_CHECKS=1;

3. All Orders/Invoices etc.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `gift_message`;
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 `reporting_orders`;
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 `tax_order_aggregated_created`;
TRUNCATE TABLE `tax_order_aggregated_updated`;
TRUNCATE TABLE `vault_payment_token_order_payment_link`;

TRUNCATE TABLE `sequence_order_0`;
TRUNCATE TABLE `sequence_invoice_0`;
TRUNCATE TABLE `sequence_shipment_0`;
TRUNCATE TABLE `sequence_creditmemo_0`;
TRUNCATE TABLE `sequence_order_1`;
TRUNCATE TABLE `sequence_invoice_1`;
TRUNCATE TABLE `sequence_shipment_1`;
TRUNCATE TABLE `sequence_creditmemo_1`;
/*Add According to your store ids */;
/*example: TRUNCATE TABLE `sequence_order_2`; */;

ALTER TABLE `sales_order` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS = 1;

4. All Logging & Operation & Report Data

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `magento_logging_event`;
TRUNCATE TABLE `magento_logging_event_changes`;
TRUNCATE TABLE `magento_operation`;
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`;


SET FOREIGN_KEY_CHECKS = 1;

5. Clearing all review

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_detail`;
TRUNCATE TABLE `review_entity`;
TRUNCATE TABLE `review_entity_summary`;
TRUNCATE TABLE `review_status`;
TRUNCATE TABLE `review_store`;

SET FOREIGN_KEY_CHECKS = 1;

6. Clearing all wishlist

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `wishlist`;
TRUNCATE TABLE `wishlist_item`;
TRUNCATE TABLE `wishlist_item_option`;

ALTER TABLE `wishlist` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS = 1;

7. Clearing all search result

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `catalogsearch_fulltext_scope1`;
TRUNCATE TABLE `search_query`;
TRUNCATE TABLE `search_synonyms`;

SET FOREIGN_KEY_CHECKS = 1;

If you want to remove Magento2 sample data then run the below command

bin/magento sampledata:remove