Find Duplicate Url In Magento

Find Duplicate Product Url

    SELECT Count(*),value
    FROM   catalog_product_entity_varchar v
    WHERE  EXISTS (SELECT *
        FROM   eav_attribute a
        WHERE  attribute_code = "url_key"
        AND v.attribute_id = a.attribute_id
        AND EXISTS (SELECT *
                    FROM   eav_entity_type e WHERE 
                    entity_type_code = "catalog_product"
                    AND a.entity_type_id = e.entity_type_id
        ))
        AND store_id = 0
        GROUP  BY v.value
        HAVING Count(*) > 1

Find Duplicate Category Url

    SELECT Count(*),value
    FROM   catalog_category_entity_varchar v
    WHERE  EXISTS (SELECT *
           FROM   eav_attribute a
           WHERE  attribute_code = "url_key"
                  AND v.attribute_id = a.attribute_id
                  AND EXISTS (SELECT *
                              FROM   eav_entity_type e
                              WHERE  entity_type_code = "catalog_category"
                                     AND a.entity_type_id = e.entity_type_id
                             ))
           AND store_id = 0
    GROUP  BY v.value
    HAVING Count(*) > 1

Update Category Data

For review purposes, find all category with uppercase url keys

    SELECT *
    FROM   catalog_category_entity_varchar v
    WHERE  `value` REGEXP BINARY '[A-Z]'
           AND EXISTS (SELECT *
               FROM   eav_attribute a
               WHERE  attribute_code = "url_key"
                      AND v.attribute_id = a.attribute_id
                      AND EXISTS (SELECT *
                                  FROM   eav_entity_type e
                                  WHERE  entity_type_code =
                                         "catalog_category"
                                         AND a.entity_type_id =
                                             e.entity_type_id));

Update all category url keys that have uppercase values to make them have lowercase values

    UPDATE catalog_category_entity_varchar v
    SET    `value` = Lower(`value`)
    WHERE  EXISTS (SELECT *
                   FROM   eav_attribute a
                   WHERE  attribute_code = "url_key"
                          AND v.attribute_id = a.attribute_id
                          AND EXISTS (SELECT *
                                  FROM   eav_entity_type e
                                  WHERE  entity_type_code = "catalog_category"
                                         AND a.entity_type_id = e.entity_type_id
                                     ))
           AND `value` REGEXP BINARY '[A-Z]';

Update Product Data

For review purposes, find all products with uppercase url keys

    SELECT *
    FROM   catalog_product_entity_varchar v
    WHERE  `value` REGEXP BINARY '[A-Z]'
           AND EXISTS (SELECT *
                FROM   eav_attribute a
                WHERE  attribute_code = "url_key"
                    AND v.attribute_id = a.attribute_id
                    AND EXISTS (SELECT *
                                FROM   eav_entity_type e
                                WHERE  entity_type_code =
                                        "catalog_product"
                                        AND a.entity_type_id = e.entity_type_id));

Update all product url keys that have uppercase values to make them have lowercase values

    UPDATE catalog_product_entity_varchar v
    SET    `value` = Lower(`value`)
    WHERE  EXISTS (SELECT *
                   FROM   eav_attribute a
                   WHERE  attribute_code = "url_key"
                          AND v.attribute_id = a.attribute_id
                          AND EXISTS (SELECT *
                                      FROM   eav_entity_type e
                                      WHERE  entity_type_code = "catalog_product"
                                             AND a.entity_type_id =
                                     e.entity_type_id))
           AND `value` REGEXP BINARY '[A-Z]';

Update url_rewrite Data for magento 1

For review purposes, find all url_rewrite entries with uppercase url keys

    SELECT * FROM core_url_rewrite WHERE  request_path REGEXP BINARY '[A-Z]';  

Update all url keys that have uppercase values to make them have lowercase values

    UPDATE core_url_rewrite
        SET    `request_path` = Lower(`request_path`)
        WHERE  `request_path` REGEXP BINARY '[A-Z]';  

Update url_rewrite Data for magento 2

For review purposes, find all product url_rewrite entries with uppercase url keys

    SELECT *
    FROM   url_rewrite
    WHERE request_path REGEXP BINARY '[A-Z]';

Update all product url keys that have uppercase values to make them have lowercase values

    UPDATE url_rewrite
    SET    `request_path` = Lower(`request_path`)
    WHERE  `request_path` REGEXP BINARY '[A-Z]';