Get list of disabled products in Magento 2 with SQL query?

Question

How can I get a list of disabled products in magento 2 using sql query?

Answer 1

To get entity ids and stores only I used:

SELECT entity_id, store_id FROM catalog_product_entity_int
WHERE value = 2 AND attribute_id = (
  SELECT attribute_id FROM eav_attribute
  WHERE entity_type_id=4 AND attribute_code='status'
)

Value 2 means disabled. Value 1 means enabled.

You can use following query to get also product names:

SELECT t_int.entity_id, t_int.store_id, t_varchar.value FROM catalog_product_entity_int AS t_int
JOIN catalog_product_entity_varchar as t_varchar ON t_varchar.entity_id = t_int.entity_id 
WHERE t_int.value = 2 AND t_int.attribute_id = (
    SELECT attribute_id FROM eav_attribute
    WHERE entity_type_id=4 AND attribute_code='status'
) AND t_varchar.attribute_id = (
    SELECT attribute_id FROM eav_attribute
    WHERE entity_type_id=4 AND attribute_code='name'
)

Answer 2

For Magento 2.2x version, use row_id instead of entity_id in the above sql