Export category url keys from Magento 2 database

During one of our recent Magento 2.x projects, we came across a request where the client needed a list of all category names and their url keys.

The M2 webshop had thousands of categories saved across multiple store-views. Fetching category url keys manually from Magento backend or frontend was not an option. Magento 2 does not have an in-built exporter that can export category information. We also did not want to install a third-party extension just for this one time export.
Our developers came up with a quick and simple query that we ran on Magento database and got all category names and their url keys listed down. We also added category id and store id to the list so that the client can recognise the categories easily.

In this post, we will share that simple query with you so that you too can export your category names and url keys easily.

Open your Magento 2.x database using phpMyAdmin and open the query window there. In the query window, copy paste below SQL query and then Submit Query.

SELECT
  DISTINCT u.entity_id AS id,
  u.store_id AS store_id,
  n.value AS name,
  u.value AS url_key
FROM
  catalog_category_entity_varchar n,
  catalog_category_entity_varchar u
WHERE
  u.entity_id = n.entity_id
  AND n.attribute_id IN (
    SELECT
      `attribute_id`
    FROM
      eav_attribute
    WHERE
      attribute_code = 'name'
      AND entity_type_id IN (
        SELECT
          entity_type_id
        FROM
          eav_entity_type
        WHERE
          entity_type_code = 'catalog_category'
      )
  )
  AND u.attribute_id IN (
    SELECT
      `attribute_id`
    FROM
      eav_attribute
    WHERE
      attribute_code = 'url_key'
      AND entity_type_id IN (
        SELECT
          entity_type_id
        FROM
          eav_entity_type
        WHERE
          entity_type_code = 'catalog_category'
      )
  );

Note: this SQL query was written for standard Magento 2.x database. You can adjust it accordingly if you have customised concerned Magento tables in your Magento build.

It will list down information in 4 columns: id, store_id, name, url_key

You can then either export this list or click on Copy to clipboard and paste it in a CSV or MS Excel file. This will give you a list of all categories that are present in your webshop along with their names and url keys.

You can compare it with categories in your Magento 2.x backoffice to be sure.

Feel free to contact Hungersoft for all your Magento development related requirements. Our team will always find smart and efficient solutions for you.