Fetch Category SEO Magento 2 from MySQL

Needed to retrieve all the Meta Titles, Descriptions & Keywords from Magento’s vanilla install. There is no method to importing SEO Data for Categories in Magento 2 without a Third Party plugin.
Hopefully this query will help those extract the info (Saves you going through the idea UI!) and getting the relevant categories to update.

Replace the following for this to work!

  • Database Prefix of ‘magento’.
  • Replace “Your store name” with the Target ‘Store name’ saved for your Store front.
SELECT 
    catentity.entity_id AS category_id,
    (SELECT 
            tvar.value
        FROM
            magento.catalog_category_entity_varchar tvar
                JOIN
            magento.catalog_category_entity cats ON (cats.row_id = tvar.row_id)
                JOIN
            magento.eav_attribute attr ON (attr.attribute_id = tvar.attribute_id)
        WHERE
            cats.entity_id = catentity.entity_id
                AND attr.attribute_code = 'name') AS category_name,
    (SELECT 
            tvar.value
        FROM
            magento.catalog_category_entity_varchar tvar
                JOIN
            magento.catalog_category_entity cats ON (cats.row_id = tvar.row_id)
                JOIN
            magento.eav_attribute attr ON (attr.attribute_id = tvar.attribute_id)
        WHERE
            cats.entity_id = catentity.entity_id
                AND attr.attribute_code = 'meta_title') AS meta_title,
    (SELECT 
            tvar.value
        FROM
            magento.catalog_category_entity_text tvar
                JOIN
            magento.catalog_category_entity cats ON (cats.row_id = tvar.row_id)
                JOIN
            magento.eav_attribute attr ON (attr.attribute_id = tvar.attribute_id)
        WHERE
            cats.entity_id = catentity.entity_id
                AND attr.attribute_code = 'meta_description') AS meta_description,
    (SELECT 
            tvar.value
        FROM
            magento.catalog_category_entity_varchar tvar
                JOIN
            magento.catalog_category_entity cats ON (cats.row_id = tvar.row_id)
                JOIN
            magento.eav_attribute attr ON (attr.attribute_id = tvar.attribute_id)
        WHERE
            cats.entity_id = catentity.entity_id
                AND attr.attribute_code = 'meta_title') AS meta_title
FROM
    magento.catalog_category_entity catentity
WHERE
    catentity.path LIKE CONCAT('1/',
            (SELECT 
                    root_category_id
                FROM
                    magento.store_group sites
                WHERE
                    name = 'Your Store Name'),
            '/%')