Magento doesn’t unfortunately provide anything out of the box to check whether attribute options have been used, in our particular case we had a lot of duplicated options that had been created by a third party integration and we needed to remove any that weren’t in use.
We turned to google and came across a Stack Exchange question here.
In case the question ever disappears we’ve copied the answer that worked for us here. Please be careful when running this sql command, we’d suggest testing it on a development/staging version of your website and then rolling out the changes from their – and as always make sure you backup your site before you run anything like this:
DELETE o, v
FROM `eav_attribute` a
INNER JOIN `eav_attribute_option` o ON a.`attribute_id` = o.`attribute_id`
INNER JOIN `eav_attribute_option_value` v ON v.`option_id` = o.`option_id`
INNER JOIN `eav_entity_type` t ON t.`entity_type_id` = a.`entity_type_id`
LEFT JOIN `catalog_product_entity_int` pi ON o.`option_id` = pi.`value` AND o.`attribute_id` = pi.`attribute_id`
LEFT JOIN `catalog_product_entity_varchar` pv ON o.`option_id` = pv.`value` AND o.`attribute_id` = pv.`attribute_id`
WHERE pi.`entity_id` IS NULL
AND pv.`entity_id` IS NULL
AND t.`entity_type_code` = “catalog_product”