How to get products name and sku and url key using sql query in magento 2 ?



SELECT nametable.value, 
FROM   `catalog_product_entity_varchar` AS nametable 
       LEFT JOIN catalog_product_entity 
              ON nametable.entity_id = catalog_product_entity.entity_id 
WHERE  nametable.attribute_id = (SELECT attribute_id 
                                 FROM   `eav_attribute` 
                                 WHERE  `entity_type_id` = 4 
                                        AND `attribute_code` LIKE 'name') 

As mentioned like Shashank Kumrawat, Sku value is store at catalog_product_entity and name field value store at catalog_product_entity_varchar

As Magento support multi-store data, So for single SKU, multiple row can be exits.catalog_product_entity_varchar have store id field.If you >> want specific store name then just nametable.store_id = {StoreId}

  • perfect answer with explanation!!! made my day! +1 :) – SagarPPanchal Nov 1 '19 at 6:54

You can use directly sql queries in magento2 as shown on this link -

using object manager (not recommended)


Without object manager (recommended)

How to call Direct SQL Queries and join to collection In Magento2

To get SKU, you need to use table catalog_product_entity

and to get product name, need to use table catalog_product_entity_varchar

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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