How to Get Product Name, SKU, and URL Key Using SQL Query in Magento 2

databasemagento2programmaticallysql

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

Best Answer

SQL QUERY (Magento 2.1 and earlier):

SELECT nametable.value, 
           nametable.store_id, 
           catalog_product_entity.sku 
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')

SQL QUERY (Magento 2.2 and later):

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

As mentioned by Shashank Kumrawat, Sku value is stored in catalog_product_entity and name field value is stored in 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}

Related Topic