Fetching All Attributes For An Entity From The DB In One Query

Using the database to inspect EAV-based entity types can be a real pain in the ass, so here are a bunch of queries that let you inspect some popular EAV entity types easily. This practice can be applied to any EAV entity type, however as there are about 25 of them, I decided to just list the popular ones, enjoy.

Output example (shortened):

|----------------------------------+----------+----------+---------------------|
| attribute_code | type | store_id | value |
|----------------------------------+----------+----------+---------------------|
| meta_title | varchar | 0 | Sofas and Armchairs |
| name | varchar | 0 | Sofas & Armchairs |
| page_layout | varchar | 0 | NULL |
| sitemap_include | int | 0 | 1 |
| url_key | varchar | 0 | sofas-and-armchairs |
|----------------------------------+----------+----------+---------------------|

Category:

SET @entity_id = 1;
(SELECT e.attribute_code, 'varchar' AS type, v.store_id, v.value FROM catalog_category_entity_varchar v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'datetime' AS type, v.store_id, v.value FROM catalog_category_entity_datetime v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'decimal' AS type, v.store_id, v.value FROM catalog_category_entity_decimal v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'int' AS type, v.store_id, v.value FROM catalog_category_entity_int v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'text' AS type, v.store_id, v.value FROM catalog_category_entity_text v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
ORDER BY attribute_code;

Product:

SET @entity_id = 1;
(SELECT e.attribute_code, 'varchar' AS type, v.store_id, v.value FROM catalog_product_entity_varchar v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'datetime' AS type, v.store_id, v.value FROM catalog_product_entity_datetime v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'decimal' AS type, v.store_id, v.value FROM catalog_product_entity_decimal v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'int' AS type, v.store_id, v.value FROM catalog_product_entity_int v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'text' AS type, v.store_id, v.value FROM catalog_product_entity_text v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
ORDER BY attribute_code;

Customer:

SET @entity_id = 1;
(SELECT e.attribute_code, 'varchar' AS type, v.store_id, v.value FROM customer_entity_varchar v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'datetime' AS type, v.store_id, v.value FROM customer_entity_datetime v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'decimal' AS type, v.store_id, v.value FROM customer_entity_decimal v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'int' AS type, v.store_id, v.value FROM customer_entity_int v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'text' AS type, v.store_id, v.value FROM customer_entity_text v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
ORDER BY attribute_code;

Customer Address:

SET @entity_id = 1;
(SELECT e.attribute_code, 'varchar' AS type, v.store_id, v.value FROM customer_address_entity_varchar v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'datetime' AS type, v.store_id, v.value FROM customer_address_entity_datetime v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'decimal' AS type, v.store_id, v.value FROM customer_address_entity_decimal v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'int' AS type, v.store_id, v.value FROM customer_address_entity_int v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'text' AS type, v.store_id, v.value FROM customer_address_entity_text v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
ORDER BY attribute_code;

Note - these queries may not work in some graphical query tools such as MySQL Query Browser due to having two queries (one to set the variable, and another to run the query). If you want to use the queries in these tools, remove the SET statement and replace all occurrences of @entity_id with the entity_id you wish to fetch.