aurmil · June 23, 2025 13:06
diff --git a/magento2_queries.sql b/magento2_queries.sql
 -- ----------
 -- CONFIG
 -- ----------

 SELECT * FROM core_config_data ccd WHERE path = '...';

 SELECT * FROM core_config_data ccd WHERE path LIKE '...' ORDER BY path, scope_id;

 -- get distinct scopes with associated values count
 SELECT scope, COUNT(config_id) FROM core_config_data ccd GROUP BY scope;

 -- ----------
 -- PATCHS
 -- ----------

 SELECT * FROM patch_list WHERE patch_name like '...' ORDER BY patch_id DESC;

 -- ----------
 -- WIDGETS
 -- ----------

 SELECT * FROM widget_instance wi
 LEFT JOIN widget_instance_page wip ON wip.instance_id = wi.instance_id
 LEFT JOIN widget_instance_page_layout wipl ON wipl.page_id = wip.page_id
 ORDER BY wi.instance_type, wi.theme_id, wi.sort_order;

 -- get widgets still having PHP serialized parameters
 SELECT * FROM widget_instance wi 
 WHERE wi.widget_parameters regexp 'a:[0-9]' 
 OR wi.widget_parameters regexp 's:[0-9]'
 OR wi.widget_parameters regexp 'i:[0-9]';

 -- ----------
 -- INDEXERS
 -- ----------

 -- generate select query on *_cl tables, with rows count and compare version id with mview state
 SELECT CONCAT(
 'select cls.*, ms.version_id AS mview_state_version_id, if(cls.cl_version_id != ms.version_id, "DIFF", "") as version_id_diff from (', 
 GROUP_CONCAT('select "', table_name, '" as `table_name`, count(*) as rows_count, max(version_id) as cl_version_id from ', table_name SEPARATOR ' union '), 
 ') cls left join mview_state ms on ms.view_id = REGEXP_REPLACE(cls.table_name, "_cl$", "") order by cls.rows_count desc;'
 )
 FROM information_schema.tables
 WHERE table_name LIKE '%\_cl'
 GROUP BY table_schema;

 -- generate select query on enabled partial indexer tables, with rows count and distinct entities count
 SELECT CONCAT(GROUP_CONCAT(REPLACE(
 "(SELECT ms.view_id, COUNT(*) AS rows_count, COUNT(DISTINCT(cl.entity_id)) AS distinct_entities_count FROM %INDEXER%_cl cl
 INNER JOIN mview_state ms ON ms.`view_id` = '%INDEXER%'
 WHERE cl.`version_id` > ms.`version_id`)", "%INDEXER%", ms.view_id
 ) ORDER BY ms.view_id SEPARATOR "\nUNION\n"), ";") 
 FROM mview_state ms 
 WHERE ms.`mode` = 'enabled';

 -- ----------
 -- CATEGORIES
 -- ----------

 -- get some categories info
 SELECT cce.entity_id, cce.path, cce.level, cce.row_id, ccev_name.`value` AS `name`, ccev_path.`value` AS `url_path`
 FROM catalog_category_entity cce
 JOIN `eav_attribute` ea_name 
 	ON ea_name.`attribute_code` = 'name' 
 	AND ea_name.`entity_type_id` = 3
 JOIN `eav_attribute` ea_path 
 	ON ea_path.`attribute_code` = 'url_path' 
 	AND ea_path.`entity_type_id` = 3
 LEFT JOIN catalog_category_entity_varchar ccev_name
 	ON ccev_name.`row_id` = cce.`row_id`
 	AND ccev_name.`attribute_id` = ea_name.`attribute_id`
 	AND ccev_name.`store_id` = 0
 LEFT JOIN catalog_category_entity_varchar ccev_path
 	ON ccev_path.`row_id` = cce.`row_id`
 	AND ccev_path.`attribute_id` = ea_path.`attribute_id`
 ORDER BY ccev_path.`value` ASC;

 -- get categories not linked to a row in url_rewrite
 SELECT s.code as store, cce.entity_id, cce.path, cce.level, cce.row_id,
 COALESCE(cce_active.value, cce_active_def.value, 0) as is_active,
 COALESCE(cce_menu.value, cce_menu_def.value, 0) as is_in_menu
 FROM catalog_category_entity cce
 join store s -- on s.code = '...'
 join store_group sg on sg.group_id = s.group_id 
 left join url_rewrite ur 
 	on ur.entity_type = 'category' 
 	and ur.entity_id = cce.entity_id 
 	and ur.store_id = s.store_id
 left join eav_attribute ea_active 
 	on ea_active.entity_type_id = 3 
 	and ea_active.attribute_code = 'is_active'
 left join catalog_category_entity_int cce_active 
 	on cce_active.attribute_id = ea_active.attribute_id 
 	and cce_active.row_id = cce.row_id 
 	and cce_active.store_id = s.store_id
 left join catalog_category_entity_int cce_active_def 
 	on cce_active_def.attribute_id = ea_active.attribute_id 
 	and cce_active_def.row_id = cce.row_id 
 	and cce_active_def.store_id = 0
 left join eav_attribute ea_menu 
 	on ea_menu.entity_type_id = 3 
 	and ea_menu.attribute_code = 'include_in_menu'
 left join catalog_category_entity_int cce_menu 
 	on cce_menu.attribute_id = ea_menu.attribute_id 
 	and cce_menu.row_id = cce.row_id 
 	and cce_menu.store_id = s.store_id
 left join catalog_category_entity_int cce_menu_def 
 	on cce_menu_def.attribute_id = ea_menu.attribute_id 
 	and cce_menu_def.row_id = cce.row_id 
 	and cce_menu_def.store_id = 0
 WHERE cce.`path` LIKE CONCAT('1/', sg.root_category_id, '/%') 
 	AND ur.url_rewrite_id IS NULL
 ORDER BY s.code, cce.entity_id;

 -- ----------
 -- PRODUCTS
 -- ----------

 -- get product's children
 SELECT cpe_child.*
 FROM catalog_product_entity cpe
 JOIN catalog_product_super_link cpsl ON cpe.row_id = cpsl.parent_id
 JOIN catalog_product_entity cpe_child ON cpsl.product_id = cpe_child.entity_id
 WHERE cpe.sku = '...';

 -- ----------
 -- PRODUCTS ATTRIBUTES
 -- ----------

 -- get some attributes info
 SELECT ea.*, cea.*, group_concat(eas.attribute_set_name SEPARATOR ' ; ') AS attribute_sets
 FROM `eav_attribute` ea 
 JOIN `catalog_eav_attribute` cea ON cea.`attribute_id` = ea.`attribute_id`
 JOIN eav_entity_attribute eea ON eea.attribute_id = ea.attribute_id 
 JOIN eav_attribute_set eas ON eas.attribute_set_id = eea.attribute_set_id 
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
 GROUP BY ea.attribute_code
 ORDER BY ea.attribute_code;

 -- get attributes labels
 select ea.attribute_id, ea.attribute_code, ea.backend_type, ea.frontend_label, s.code AS store, eal.value AS store_label
 from eav_attribute ea 
 left join eav_attribute_label eal on eal.attribute_id = ea.attribute_id
 left join store s on s.store_id = eal.store_id
 where ea.entity_type_id = 4 and ea.attribute_code in (...)
 order by ea.attribute_code, eal.store_id;

 -- find swatch attributes
 SELECT *
 FROM `eav_attribute` ea 
 JOIN `catalog_eav_attribute` cea ON cea.`attribute_id` = ea.`attribute_id`
 WHERE cea.additional_data like '%swatch_input_type%'
 ORDER BY ea.attribute_code ;

 -- ----------
 -- PRODUCTS ATTRIBUTES VALUES
 -- ----------

 -- get attribute values
 SELECT * 
 FROM `eav_attribute` ea 
 JOIN `catalog_product_entity_varchar` cpev ON cpev.`attribute_id` = ea.`attribute_id`
 WHERE ea.`attribute_code` = '...' AND ea.`entity_type_id` = 4;

 -- get attributes distinct values
 SELECT ea.attribute_code, group_concat(DISTINCT cpev.value SEPARATOR " ; ")
 FROM `eav_attribute` ea 
 JOIN catalog_product_entity_varchar cpev ON cpev.attribute_id = ea.attribute_id
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` IN (...)
 GROUP BY ea.attribute_code
 ORDER BY ea.attribute_code;

 -- get attribute option values
 SELECT *
 FROM `eav_attribute` ea 
 JOIN eav_attribute_option eao ON eao.attribute_id = ea.attribute_id
 JOIN eav_attribute_option_value eaov ON eaov.option_id = eao.option_id
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` = '...';

 -- get attributes distinct option values
 SELECT ea.attribute_code, group_concat(DISTINCT eaov.value SEPARATOR " ; ")
 FROM `eav_attribute` ea 
 JOIN eav_attribute_option eao ON eao.attribute_id = ea.attribute_id
 JOIN eav_attribute_option_value eaov ON eaov.option_id = eao.option_id
 WHERE ea.`entity_type_id` = 4 
  AND ea.frontend_input LIKE '%select' 
  AND ea.`attribute_code` IN (...)
 GROUP BY ea.attribute_code
 ORDER BY ea.attribute_code;

 -- non empty values count, for some attributes
 select ea.attribute_id, ea.attribute_code, ea.backend_type, ea.frontend_label, count(cpei.value_id) as values_count
 from eav_attribute ea 
 left join catalog_product_entity_int cpei on cpei.attribute_id = ea.attribute_id 
 where ea.entity_type_id = 4 and ea.backend_type = 'int' and ea.attribute_code in (...) and cpei.value is not null and cpei.value != ''
 group by cpei.attribute_id 
 union
 select ea.attribute_id, ea.attribute_code, ea.backend_type, ea.frontend_label, count(cpet.value_id) as values_count
 from eav_attribute ea 
 left join catalog_product_entity_text cpet on cpet.attribute_id = ea.attribute_id 
 where ea.entity_type_id = 4 and ea.backend_type = 'text' and ea.attribute_code in (...) and cpet.value is not null and cpet.value != ''
 group by cpet.attribute_id 
 union
 select ea.attribute_id, ea.attribute_code, ea.backend_type, ea.frontend_label, count(cpev.value_id) as values_count
 from eav_attribute ea 
 left join catalog_product_entity_varchar cpev on cpev.attribute_id = ea.attribute_id 
 where ea.entity_type_id = 4 and ea.backend_type = 'varchar' and ea.attribute_code in (...) and cpev.value is not null and cpev.value != ''
 group by cpet.attribute_id 
 order by attribute_code ;

 -- type of products associated to non empty values, for some attributes
 SELECT ea.attribute_code, group_concat(distinct cpe.type_id) as related_types
 FROM `eav_attribute` ea 
 join catalog_product_entity_int cpei on ea.attribute_id = cpei.attribute_id and cpei.value is not null
 join catalog_product_entity cpe on cpei.row_id = cpe.row_id
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
 group by ea.attribute_code
 union
 SELECT ea.attribute_code, group_concat(distinct cpe.type_id) as related_types
 FROM `eav_attribute` ea 
 join catalog_product_entity_text cpet on ea.attribute_id = cpet.attribute_id and cpet.value is not null and cpet.value != ''
 join catalog_product_entity cpe on cpet.row_id = cpe.row_id
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
 group by ea.attribute_code
 union
 SELECT ea.attribute_code, group_concat(distinct cpe.type_id) as related_types
 FROM `eav_attribute` ea 
 join catalog_product_entity_varchar cpev on ea.attribute_id = cpev.attribute_id and cpev.value is not null and cpev.value != ''
 join catalog_product_entity cpe on cpev.row_id = cpe.row_id
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
 group by ea.attribute_code
 order by attribute_code;

 -- non empty values count, for some attributes, associated to configurable products
 SELECT ea.attribute_code, count(cpei.value_id)
 FROM `eav_attribute` ea 
 join catalog_product_entity_int cpei on ea.attribute_id = cpei.attribute_id and cpei.value is not null
 join catalog_product_entity cpe on cpei.row_id = cpe.row_id and cpe.type_id = 'configurable'
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
 group by ea.attribute_code
 union
 SELECT ea.attribute_code, count(cpet.value_id)
 FROM `eav_attribute` ea 
 join catalog_product_entity_text cpet on ea.attribute_id = cpet.attribute_id and cpet.value is not null and cpet.value != ''
 join catalog_product_entity cpe on cpet.row_id = cpe.row_id and cpe.type_id = 'configurable'
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
 group by ea.attribute_code
 union
 SELECT ea.attribute_code, count(cpev.value_id)
 FROM `eav_attribute` ea 
 join catalog_product_entity_varchar cpev on ea.attribute_id = cpev.attribute_id and cpev.value is not null and cpev.value != ''
 join catalog_product_entity cpe on cpev.row_id = cpe.row_id and cpe.type_id = 'configurable'
 WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
 group by ea.attribute_code
 order by attribute_code;

 -- get products name
 select cpe.*, cpev.value
 from catalog_product_entity cpe
 join eav_attribute ea on ea.attribute_code = 'name' and ea.entity_type_id = 4
 join catalog_product_entity_varchar cpev on cpe.row_id = cpev.row_id and cpev.attribute_id = ea.attribute_id and cpev.store_id = 0
 where cpe.sku in (...);

 -- get configurable products "image" values
 select cpe.*, cpev.value
 from catalog_product_entity cpe
 join eav_attribute ea on ea.attribute_code = 'image' and ea.entity_type_id = 4
 join catalog_product_entity_varchar cpev on cpe.row_id = cpev.row_id and cpev.attribute_id = ea.attribute_id AND cpev.`value` != 'no_selection' AND cpe.`type_id` = 'configurable'
 where cpe.sku in (...);

 -- get products info when image != small_image
 select cpe.entity_id, cpe.sku, cpe.row_id, cpe_parent.sku, cpev_name.value , cpev_img.value, cpev_smimg.value
 from catalog_product_entity cpe 
 join eav_attribute ea_img on ea_img.entity_type_id = 4 and ea_img.attribute_code = 'image'
 join eav_attribute ea_smimg on ea_smimg.entity_type_id = 4 and ea_smimg.attribute_code = 'small_image'
 join catalog_product_entity_varchar cpev_img on cpev_img.attribute_id = ea_img.attribute_id and cpev_img.row_id = cpe.row_id 
 join catalog_product_entity_varchar cpev_smimg on cpev_smimg.attribute_id = ea_smimg.attribute_id and cpev_smimg.row_id = cpe.row_id
 left join catalog_product_super_link cpsl on cpsl.product_id = cpe.entity_id 
 join catalog_product_entity cpe_parent on cpe_parent.row_id = cpsl.parent_id 
 join eav_attribute ea_name on ea_name.entity_type_id = 4 and ea_name.attribute_code = 'name'
 left join catalog_product_entity_varchar cpev_name on cpev_name.attribute_id = ea_name.attribute_id 
 	and cpev_name.row_id = cpe_parent.row_id and cpev_name.store_id = 0
 where cpev_img.value != cpev_smimg.value;
	-- ----------
	-- CONFIG
	-- ----------

	SELECT * FROM core_config_data ccd WHERE path = '...';

	SELECT * FROM core_config_data ccd WHERE path LIKE '...' ORDER BY path, scope_id;

	-- get distinct scopes with associated values count
	SELECT scope, COUNT(config_id) FROM core_config_data ccd GROUP BY scope;

	-- ----------
	-- PATCHS
	-- ----------

	SELECT * FROM patch_list WHERE patch_name like '...' ORDER BY patch_id DESC;

	-- ----------
	-- WIDGETS
	-- ----------

	SELECT * FROM widget_instance wi
	LEFT JOIN widget_instance_page wip ON wip.instance_id = wi.instance_id
	LEFT JOIN widget_instance_page_layout wipl ON wipl.page_id = wip.page_id
	ORDER BY wi.instance_type, wi.theme_id, wi.sort_order;

	-- get widgets still having PHP serialized parameters
	SELECT * FROM widget_instance wi
	WHERE wi.widget_parameters regexp 'a:[0-9]'
	OR wi.widget_parameters regexp 's:[0-9]'
	OR wi.widget_parameters regexp 'i:[0-9]';

	-- ----------
	-- INDEXERS
	-- ----------

	-- generate select query on *_cl tables, with rows count and compare version id with mview state
	SELECT CONCAT(
	'select cls.*, ms.version_id AS mview_state_version_id, if(cls.cl_version_id != ms.version_id, "DIFF", "") as version_id_diff from (',
	GROUP_CONCAT('select "', table_name, '" as `table_name`, count(*) as rows_count, max(version_id) as cl_version_id from ', table_name SEPARATOR ' union '),
	') cls left join mview_state ms on ms.view_id = REGEXP_REPLACE(cls.table_name, "_cl$", "") order by cls.rows_count desc;'
	)
	FROM information_schema.tables
	WHERE table_name LIKE '%\_cl'
	GROUP BY table_schema;

	-- generate select query on enabled partial indexer tables, with rows count and distinct entities count
	SELECT CONCAT(GROUP_CONCAT(REPLACE(
	"(SELECT ms.view_id, COUNT(*) AS rows_count, COUNT(DISTINCT(cl.entity_id)) AS distinct_entities_count FROM %INDEXER%_cl cl
	INNER JOIN mview_state ms ON ms.`view_id` = '%INDEXER%'
	WHERE cl.`version_id` > ms.`version_id`)", "%INDEXER%", ms.view_id
	) ORDER BY ms.view_id SEPARATOR "\nUNION\n"), ";")
	FROM mview_state ms
	WHERE ms.`mode` = 'enabled';

	-- ----------
	-- CATEGORIES
	-- ----------

	-- get some categories info
	SELECT cce.entity_id, cce.path, cce.level, cce.row_id, ccev_name.`value` AS `name`, ccev_path.`value` AS `url_path`
	FROM catalog_category_entity cce
	JOIN `eav_attribute` ea_name
	ON ea_name.`attribute_code` = 'name'
	AND ea_name.`entity_type_id` = 3
	JOIN `eav_attribute` ea_path
	ON ea_path.`attribute_code` = 'url_path'
	AND ea_path.`entity_type_id` = 3
	LEFT JOIN catalog_category_entity_varchar ccev_name
	ON ccev_name.`row_id` = cce.`row_id`
	AND ccev_name.`attribute_id` = ea_name.`attribute_id`
	AND ccev_name.`store_id` = 0
	LEFT JOIN catalog_category_entity_varchar ccev_path
	ON ccev_path.`row_id` = cce.`row_id`
	AND ccev_path.`attribute_id` = ea_path.`attribute_id`
	ORDER BY ccev_path.`value` ASC;

	-- get categories not linked to a row in url_rewrite
	SELECT s.code as store, cce.entity_id, cce.path, cce.level, cce.row_id,
	COALESCE(cce_active.value, cce_active_def.value, 0) as is_active,
	COALESCE(cce_menu.value, cce_menu_def.value, 0) as is_in_menu
	FROM catalog_category_entity cce
	join store s -- on s.code = '...'
	join store_group sg on sg.group_id = s.group_id
	left join url_rewrite ur
	on ur.entity_type = 'category'
	and ur.entity_id = cce.entity_id
	and ur.store_id = s.store_id
	left join eav_attribute ea_active
	on ea_active.entity_type_id = 3
	and ea_active.attribute_code = 'is_active'
	left join catalog_category_entity_int cce_active
	on cce_active.attribute_id = ea_active.attribute_id
	and cce_active.row_id = cce.row_id
	and cce_active.store_id = s.store_id
	left join catalog_category_entity_int cce_active_def
	on cce_active_def.attribute_id = ea_active.attribute_id
	and cce_active_def.row_id = cce.row_id
	and cce_active_def.store_id = 0
	left join eav_attribute ea_menu
	on ea_menu.entity_type_id = 3
	and ea_menu.attribute_code = 'include_in_menu'
	left join catalog_category_entity_int cce_menu
	on cce_menu.attribute_id = ea_menu.attribute_id
	and cce_menu.row_id = cce.row_id
	and cce_menu.store_id = s.store_id
	left join catalog_category_entity_int cce_menu_def
	on cce_menu_def.attribute_id = ea_menu.attribute_id
	and cce_menu_def.row_id = cce.row_id
	and cce_menu_def.store_id = 0
	WHERE cce.`path` LIKE CONCAT('1/', sg.root_category_id, '/%')
	AND ur.url_rewrite_id IS NULL
	ORDER BY s.code, cce.entity_id;

	-- ----------
	-- PRODUCTS
	-- ----------

	-- get product's children
	SELECT cpe_child.*
	FROM catalog_product_entity cpe
	JOIN catalog_product_super_link cpsl ON cpe.row_id = cpsl.parent_id
	JOIN catalog_product_entity cpe_child ON cpsl.product_id = cpe_child.entity_id
	WHERE cpe.sku = '...';

	-- ----------
	-- PRODUCTS ATTRIBUTES
	-- ----------

	-- get some attributes info
	SELECT ea., cea., group_concat(eas.attribute_set_name SEPARATOR ' ; ') AS attribute_sets
	FROM `eav_attribute` ea
	JOIN `catalog_eav_attribute` cea ON cea.`attribute_id` = ea.`attribute_id`
	JOIN eav_entity_attribute eea ON eea.attribute_id = ea.attribute_id
	JOIN eav_attribute_set eas ON eas.attribute_set_id = eea.attribute_set_id
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
	GROUP BY ea.attribute_code
	ORDER BY ea.attribute_code;

	-- get attributes labels
	select ea.attribute_id, ea.attribute_code, ea.backend_type, ea.frontend_label, s.code AS store, eal.value AS store_label
	from eav_attribute ea
	left join eav_attribute_label eal on eal.attribute_id = ea.attribute_id
	left join store s on s.store_id = eal.store_id
	where ea.entity_type_id = 4 and ea.attribute_code in (...)
	order by ea.attribute_code, eal.store_id;

	-- find swatch attributes
	SELECT *
	FROM `eav_attribute` ea
	JOIN `catalog_eav_attribute` cea ON cea.`attribute_id` = ea.`attribute_id`
	WHERE cea.additional_data like '%swatch_input_type%'
	ORDER BY ea.attribute_code ;

	-- ----------
	-- PRODUCTS ATTRIBUTES VALUES
	-- ----------

	-- get attribute values
	SELECT *
	FROM `eav_attribute` ea
	JOIN `catalog_product_entity_varchar` cpev ON cpev.`attribute_id` = ea.`attribute_id`
	WHERE ea.`attribute_code` = '...' AND ea.`entity_type_id` = 4;

	-- get attributes distinct values
	SELECT ea.attribute_code, group_concat(DISTINCT cpev.value SEPARATOR " ; ")
	FROM `eav_attribute` ea
	JOIN catalog_product_entity_varchar cpev ON cpev.attribute_id = ea.attribute_id
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` IN (...)
	GROUP BY ea.attribute_code
	ORDER BY ea.attribute_code;

	-- get attribute option values
	SELECT *
	FROM `eav_attribute` ea
	JOIN eav_attribute_option eao ON eao.attribute_id = ea.attribute_id
	JOIN eav_attribute_option_value eaov ON eaov.option_id = eao.option_id
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` = '...';

	-- get attributes distinct option values
	SELECT ea.attribute_code, group_concat(DISTINCT eaov.value SEPARATOR " ; ")
	FROM `eav_attribute` ea
	JOIN eav_attribute_option eao ON eao.attribute_id = ea.attribute_id
	JOIN eav_attribute_option_value eaov ON eaov.option_id = eao.option_id
	WHERE ea.`entity_type_id` = 4
	AND ea.frontend_input LIKE '%select'
	AND ea.`attribute_code` IN (...)
	GROUP BY ea.attribute_code
	ORDER BY ea.attribute_code;

	-- non empty values count, for some attributes
	select ea.attribute_id, ea.attribute_code, ea.backend_type, ea.frontend_label, count(cpei.value_id) as values_count
	from eav_attribute ea
	left join catalog_product_entity_int cpei on cpei.attribute_id = ea.attribute_id
	where ea.entity_type_id = 4 and ea.backend_type = 'int' and ea.attribute_code in (...) and cpei.value is not null and cpei.value != ''
	group by cpei.attribute_id
	union
	select ea.attribute_id, ea.attribute_code, ea.backend_type, ea.frontend_label, count(cpet.value_id) as values_count
	from eav_attribute ea
	left join catalog_product_entity_text cpet on cpet.attribute_id = ea.attribute_id
	where ea.entity_type_id = 4 and ea.backend_type = 'text' and ea.attribute_code in (...) and cpet.value is not null and cpet.value != ''
	group by cpet.attribute_id
	union
	select ea.attribute_id, ea.attribute_code, ea.backend_type, ea.frontend_label, count(cpev.value_id) as values_count
	from eav_attribute ea
	left join catalog_product_entity_varchar cpev on cpev.attribute_id = ea.attribute_id
	where ea.entity_type_id = 4 and ea.backend_type = 'varchar' and ea.attribute_code in (...) and cpev.value is not null and cpev.value != ''
	group by cpet.attribute_id
	order by attribute_code ;

	-- type of products associated to non empty values, for some attributes
	SELECT ea.attribute_code, group_concat(distinct cpe.type_id) as related_types
	FROM `eav_attribute` ea
	join catalog_product_entity_int cpei on ea.attribute_id = cpei.attribute_id and cpei.value is not null
	join catalog_product_entity cpe on cpei.row_id = cpe.row_id
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
	group by ea.attribute_code
	union
	SELECT ea.attribute_code, group_concat(distinct cpe.type_id) as related_types
	FROM `eav_attribute` ea
	join catalog_product_entity_text cpet on ea.attribute_id = cpet.attribute_id and cpet.value is not null and cpet.value != ''
	join catalog_product_entity cpe on cpet.row_id = cpe.row_id
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
	group by ea.attribute_code
	union
	SELECT ea.attribute_code, group_concat(distinct cpe.type_id) as related_types
	FROM `eav_attribute` ea
	join catalog_product_entity_varchar cpev on ea.attribute_id = cpev.attribute_id and cpev.value is not null and cpev.value != ''
	join catalog_product_entity cpe on cpev.row_id = cpe.row_id
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
	group by ea.attribute_code
	order by attribute_code;

	-- non empty values count, for some attributes, associated to configurable products
	SELECT ea.attribute_code, count(cpei.value_id)
	FROM `eav_attribute` ea
	join catalog_product_entity_int cpei on ea.attribute_id = cpei.attribute_id and cpei.value is not null
	join catalog_product_entity cpe on cpei.row_id = cpe.row_id and cpe.type_id = 'configurable'
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
	group by ea.attribute_code
	union
	SELECT ea.attribute_code, count(cpet.value_id)
	FROM `eav_attribute` ea
	join catalog_product_entity_text cpet on ea.attribute_id = cpet.attribute_id and cpet.value is not null and cpet.value != ''
	join catalog_product_entity cpe on cpet.row_id = cpe.row_id and cpe.type_id = 'configurable'
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
	group by ea.attribute_code
	union
	SELECT ea.attribute_code, count(cpev.value_id)
	FROM `eav_attribute` ea
	join catalog_product_entity_varchar cpev on ea.attribute_id = cpev.attribute_id and cpev.value is not null and cpev.value != ''
	join catalog_product_entity cpe on cpev.row_id = cpe.row_id and cpe.type_id = 'configurable'
	WHERE ea.`entity_type_id` = 4 AND ea.`attribute_code` in (...)
	group by ea.attribute_code
	order by attribute_code;

	-- get products name
	select cpe.*, cpev.value
	from catalog_product_entity cpe
	join eav_attribute ea on ea.attribute_code = 'name' and ea.entity_type_id = 4
	join catalog_product_entity_varchar cpev on cpe.row_id = cpev.row_id and cpev.attribute_id = ea.attribute_id and cpev.store_id = 0
	where cpe.sku in (...);

	-- get configurable products "image" values
	select cpe.*, cpev.value
	from catalog_product_entity cpe
	join eav_attribute ea on ea.attribute_code = 'image' and ea.entity_type_id = 4
	join catalog_product_entity_varchar cpev on cpe.row_id = cpev.row_id and cpev.attribute_id = ea.attribute_id AND cpev.`value` != 'no_selection' AND cpe.`type_id` = 'configurable'
	where cpe.sku in (...);

	-- get products info when image != small_image
	select cpe.entity_id, cpe.sku, cpe.row_id, cpe_parent.sku, cpev_name.value , cpev_img.value, cpev_smimg.value
	from catalog_product_entity cpe
	join eav_attribute ea_img on ea_img.entity_type_id = 4 and ea_img.attribute_code = 'image'
	join eav_attribute ea_smimg on ea_smimg.entity_type_id = 4 and ea_smimg.attribute_code = 'small_image'
	join catalog_product_entity_varchar cpev_img on cpev_img.attribute_id = ea_img.attribute_id and cpev_img.row_id = cpe.row_id
	join catalog_product_entity_varchar cpev_smimg on cpev_smimg.attribute_id = ea_smimg.attribute_id and cpev_smimg.row_id = cpe.row_id
	left join catalog_product_super_link cpsl on cpsl.product_id = cpe.entity_id
	join catalog_product_entity cpe_parent on cpe_parent.row_id = cpsl.parent_id
	join eav_attribute ea_name on ea_name.entity_type_id = 4 and ea_name.attribute_code = 'name'
	left join catalog_product_entity_varchar cpev_name on cpev_name.attribute_id = ea_name.attribute_id
	and cpev_name.row_id = cpe_parent.row_id and cpev_name.store_id = 0
	where cpev_img.value != cpev_smimg.value;
No results found