Magento EAV Entity Inspection

Category.sql

/**
 * Magento EAV Entity Inspection
 * Inspect Category
 */
SET @entityid = '3';

SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM catalog_category_entity e
JOIN catalog_category_entity_varchar eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM catalog_category_entity e
JOIN catalog_category_entity_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'
FROM catalog_category_entity e
JOIN catalog_category_entity_decimal eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'
FROM catalog_category_entity e
JOIN catalog_category_entity_datetime eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM catalog_category_entity e
JOIN catalog_category_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid

Customer.sql

/**
 * Magento EAV Entity Inspection
 * Inspect Customer
 */
 
SET @customerid = '1';
/*
SET @customerid = (SELECT e.entity_id 
		  FROM customer_entity e
		  WHERE e.email = '[email protected]');
*/

SELECT ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM customer_entity e
JOIN customer_entity_varchar eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM customer_entity e
JOIN customer_entity_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'
FROM customer_entity e
JOIN customer_entity_decimal eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'
FROM customer_entity e
JOIN customer_entity_datetime eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM customer_entity e
JOIN customer_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid

Order.sql

/**
 * Magento EAV Entity Inspection
 * Inspect Order
 */
 
SET @entityid = '100000001';

SELECT ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM sales_order e
JOIN sales_order_varchar eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.increment_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM sales_order e
JOIN sales_order_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.increment_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'
FROM sales_order e
JOIN sales_order_decimal eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.increment_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'
FROM sales_order e
JOIN sales_order_datetime eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.increment_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM sales_order e
JOIN sales_order_text eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.increment_id = @entityid

Product.sql

/**
 * Magento EAV Entity Inspection
 * Inspect Product
 */
SET @entityid = 203;

SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_varchar eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_decimal eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_datetime eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid

Order by attribute_id