Magento2: How can I get all orders from database tables

Question

I want to get all orders record from database. How many tables I have to dump for . Kindly enlist the tables names here

Answer 1

You have to dump for these tables for all orders records:

• sales_invoice
• sales_invoice_comment
• sales_invoice_grid
• sales_invoice_item
• sales_invoiced_aggregated
• sales_invoiced_aggregated_order
• sales_order
• sales_order_address
• sales_order_aggregated_created
• sales_order_aggregated_updated
• sales_order_grid
• sales_order_item
• sales_order_payment
• sales_order_status
• sales_order_status_history
• sales_order_status_state
• sales_order_tax
• sales_order_tax_item
• sales_shipment_item
• sales_shipping_aggregated
• sales_shipping_aggregated_order

mysqldump -u root -p sales_invoice sales_invoice_comment sales_invoice_grid sales_invoice_item sales_invoiced_aggregated sales_invoiced_aggregated_order sales_order sales_order_address sales_order_aggregated_created sales_order_aggregated_updated sales_order_grid sales_order_item sales_order_payment sales_order_status sales_order_status_history sales_order_status_state sales_order_tax sales_order_tax_item sales_shipment_item sales_shipping_aggregated sales_shipping_aggregated_order > orders.sql

Answer 2

We can get the all order details by this SQL query -

mysqldump -u root -p database_name 
sales_bestsellers_aggregated_daily
sales_bestsellers_aggregated_monthly
sales_bestsellers_aggregated_yearly
sales_creditmemo
sales_creditmemo_comment
sales_creditmemo_grid
sales_creditmemo_item
sales_invoice
sales_invoiced_aggregated
sales_invoiced_aggregated_order
sales_invoice_comment
sales_invoice_grid
sales_invoice_item
sales_order
sales_order_address
sales_order_aggregated_created
sales_order_aggregated_updated
sales_order_grid
sales_order_gst
sales_order_item
sales_order_item_gst
sales_order_payment
sales_order_status
sales_order_status_history
sales_order_status_label
sales_order_status_state
sales_order_tax
sales_order_tax_item
sales_payment_transaction
sales_refunded_aggregated
sales_refunded_aggregated_order
sales_sequence_meta
sales_sequence_profile
sales_shipment
sales_shipment_comment
sales_shipment_grid
sales_shipment_item
sales_shipment_track
sales_shipping_aggregated
sales_shipping_aggregated_order > orderData.sql

I hope this will helps you to get only export the order details

Answer 3

You have to include the following tables,
these are basically the tables storing and handling the orders data:

sales_order //responsible for all orders
sales_order_item //responsible for all order items associated to the order

Answer 4

Below are tables related to order in Magento:

• sales_order;

• sales_order_address;

• sales_order_aggregated_created;

• sales_order_aggregated_updated;

• sales_order_grid;

• sales_order_item;

• sales_order_payment;

• sales_order_status_history;

• sales_order_tax;

• sales_order_tax_item;

Answer 5

If you are looking to remove all test orders / data your best to run the below which will do this.

SET FOREIGN_KEY_CHECKS=0;

# Clean order history
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;

# Clean order infos
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`;

# Clean cart infos
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`;

# 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;

Answer 6

sales_order stores main order data

Answer 7

You can get the all the order related tables using below query

mysqldump -u root -p database_name sales_bestsellers_aggregated_daily sales_bestsellers_aggregated_monthly sales_bestsellers_aggregated_yearly sales_creditmemo sales_creditmemo_comment sales_creditmemo_grid sales_creditmemo_item sales_invoice sales_invoice_comment sales_invoice_grid sales_invoice_item sales_invoiced_aggregated sales_invoiced_aggregated_order sales_order sales_order_address sales_order_aggregated_created sales_order_aggregated_updated sales_order_grid sales_order_item sales_order_payment sales_order_status sales_order_status_history sales_order_status_label sales_order_status_state sales_order_tax sales_order_tax_item sales_payment_transaction sales_refunded_aggregated sales_refunded_aggregated_order sales_sequence_meta sales_sequence_profile sales_shipment sales_shipment_comment sales_shipment_grid sales_shipment_item sales_shipment_track sales_shipping_aggregated sales_shipping_aggregated_order > OrderData.sql