Find all products in Magento 2 with more than 1 product image

Question

Looking for some SQL which would list all SKU's with more than 1 product image attached to it.

Ideally I would also like to delete all images which are not used as base/small/thumbnail, but happy to do that that in the backend if that's easier.

The relevant tables seem to be catalog_product_entity_media_gallery, catalog_product_entity_media_gallery_value and catalog_product_entity_varchar.

Answer 1

The following SQL finds all images in catalog_product_entity_media_gallery which are not used as base/small/thumbnail:

SELECT
  gal.value_id,
  gal.value
FROM catalog_product_entity_media_gallery gal
  LEFT JOIN catalog_product_entity_varchar ent ON ent.value = gal.value
WHERE ent.value IS NULL;

Exported this as text file and then created a script which deleted all entries from:

• catalog_product_entity_media_gallery
• catalog_product_entity_media_gallery_value

where value_id was equal to the ones in the file generated above.