How to list all products together with their activated/deactivated state using plain SQL?

Question

I want a list of all products together with a column saying either deactivated or activated for the product. I want to use plain SQL.

I tried for a while but did not find that information. Any ideas?

Thanks!

Answer 1

The sql code to do this is:

SELECT `entity_id` AS product_id, `value` AS product_status 
FROM catalog_product_entity_int
WHERE entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product') 
AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'status' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'));

Though this sql code works you are (usually) much better off doing this programmatically:

$products = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect('status') //whatever attributes you want to get here
    ->load();

// loop through each one and get their info
foreach($products as $prod){
    echo $prod->getId();
    echo $prod->getName();
    echo $prod->getStatus();
 }

Answer 2

Enable Use Flat Catalog Product in System > Configuration > Catalog > Frontend and use

SELECT *
FROM `catalog_product_flat`;