I'm looking for the exact SQL query that lists products - or at least a list of tables which it hits. I was looking for it in the source code, but could not find it.

2 Answers 2


you can add this to the app/code/Magento/Catalog/view/frontend/templates/product/list.phtml file:

echo $_productCollection->getSelect();

this should print the query on the screen.

  • Thanks, however, there is not such path for me. No code in app. Using Magento ver. 2.0.4.
    – Dyin
    Commented Apr 28, 2016 at 14:28
  • if you installed m2 from the archive you will find the file in vendor/magento-catalog/view...
    – Marius
    Commented Apr 28, 2016 at 14:47

List of all SKUs:

SELECT * FROM `catalog_product_entity`;

List of all product names:

SELECT * FROM `catalog_product_entity_varchar`;

As Mr. Jute explains in his blog post List of Product tables in magento:

Main table for products in magento:


Above table is an act as main table, for that child tables are staring with “catalog_product_entity_” prefix.

catalog_product_entity_datetime – Product – date attribute are stored here. Ex: New From, New To catalog_product_entity_decimal – Product price related values. catalog_product_entity_gallery catalog_product_entity_int – Product Status, Visiblity etc.. catalog_product_entity_media_gallery – product images path, Thumb , small, etc catalog_product_entity_media_gallery_value – Product image,product image position, title info & position of image info, etc catalog_product_entity_tier_price – Product tier product catalog_product_entity_varchar – Product name, URL, etc Bundled product – information: Will get bundled product associated values from below table catalog_product_bundle_option catalog_product_bundle_option_value catalog_product_bundle_selection

Related to downloaded product information.

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

Product custom option information will get from below tables

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

Configurable product and Grouped product information will get it from below tables

catalog_product_super_attribute; catalog_product_super_attribute_label; catalog_product_super_attribute_pricing; catalog_product_super_link;

catalog_product_enabled_index; catalog_product_website – Product website

Product currency inventory information: (Inventory)

cataloginventory_stock cataloginventory_stock_item cataloginventory_stock_status – Currently Product is instock or outof stock

catalog_category_product – Relationship between categorty & product. What ant all product are mapped with which category also the possition of product under the category

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.