Magento 2 URL Rewrite full reindex

urlRewriteReindex.php

<?php
/*
###############################################
WARNING: this script truncates url rewrite tables and generates system url rewrites;
ALL CUSTOM URL REWRITES WILL BE DELETED
###############################################
*/

const BATCH_SIZE = 5;

require_once './app/bootstrap.php';

$_SERVER[\Magento\Store\Model\StoreManager::PARAM_RUN_CODE] = 'default';
$_SERVER[\Magento\Store\Model\StoreManager::PARAM_RUN_TYPE] = 'store';

/** @var \Magento\Framework\App\Bootstrap $bootstrap */
$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);

/** @var \Magento\Framework\ObjectManager\ObjectManager $objectManager */
$objectManager = $bootstrap->getObjectManager();

/** @var \Magento\Framework\App\State $appState */
$appState = $objectManager->get('Magento\Framework\App\State');
$appState->setAreaCode(\Magento\Framework\App\Area::AREA_ADMIN);


/* TRUNCATING url_rewrite TABLES */

/** @var \Magento\Framework\Module\ModuleResource $moduleResource */
$moduleResource = $objectManager->get('Magento\Framework\Module\ModuleResource');
/** @var \Magento\Framework\DB\Adapter\Pdo\Mysql $connection */
$connection = $moduleResource->getConnection();
$connection->query('set foreign_key_checks = 0');
$connection->truncateTable($moduleResource->getTable('catalog_url_rewrite_product_category'));
$connection->truncateTable($moduleResource->getTable('url_rewrite'));
$connection->query('set foreign_key_checks = 1');


/* REINDEX URL REWRITES FOR CMS PAGES */

/** @var \Magento\Cms\Model\ResourceModel\Page\Collection $cmsPageCollection */
$cmsPageCollection = $objectManager->get('Magento\Cms\Model\ResourceModel\Page\Collection');

/** @var |Magento\Framework\DB\Select $select */
$select = $cmsPageCollection->getSelect();
$select->columns($cmsPageCollection->getResource()->getIdFieldName(), 'main_table')
    ->limit(BATCH_SIZE);

$offset = 0;
while ($cmsPageIds = $connection->fetchCol($select)) {

    /** @var \Magento\Cms\Model\Page $cmsPage */
    $cmsPage = $objectManager->get('Magento\Cms\Model\Page');
    $eventName = 'cms_page_save_after';

    foreach ($cmsPageIds as $cmsPageId) {
        $cmsPageId = (int)$cmsPageId;
        $cmsPage->load($cmsPageId);
        $cmsPage->setOrigData('identifier', '');

        $data = ['object' => $cmsPage];

        /** @var \Magento\Framework\Event $event */
        $event = new \Magento\Framework\Event($data);
        $event->setName($eventName);

        /** @var \Magento\Framework\Event\Observer $observer */
        $observer = new \Magento\Framework\Event\Observer();
        $observer->setData(array_merge(['event' => $event], $data));

        /** @var \Magento\CmsUrlRewrite\Observer\ProcessUrlRewriteSavingObserver $cmsPageProcess */
        $cmsPageProcess = $objectManager->get('Magento\CmsUrlRewrite\Observer\ProcessUrlRewriteSavingObserver');
        $cmsPageProcess->execute($observer);
    }

    $offset += BATCH_SIZE;
    $select->limit(BATCH_SIZE, $offset);
}


/* REINDEX URL REWRITES FOR CATEGORIES AND ASSIGNED PRODUCTS */

/** @var \Magento\Catalog\Model\ResourceModel\Category\Collection $categoryCollection */
$categoryCollection = $objectManager->get('Magento\Catalog\Model\ResourceModel\Category\Collection');

$offset = 0;
while ($categoryIds = $categoryCollection->getAllIds(BATCH_SIZE, $offset)) {
    /** @var \Magento\Catalog\Model\Product $product */
    $category = $objectManager->get('Magento\Catalog\Model\Category');
    $eventName = 'catalog_category_save_after';

    foreach ($categoryIds as $categoryId) {
        $categoryId = (int)$categoryId;
        $category->load($categoryId);
        $category->setOrigData('url_key', '');

        $data = ['category' => $category];

        /** @var \Magento\Framework\Event $event */
        $event = new \Magento\Framework\Event($data);
        $event->setName($eventName);

        /** @var \Magento\Framework\Event\Observer $observer */
        $observer = new \Magento\Framework\Event\Observer();
        $observer->setData(array_merge(['event' => $event], $data));

        /** @var \Magento\CatalogUrlRewrite\Observer\CategoryProcessUrlRewriteSavingObserver $categoryProcess */
        $categoryProcess = $objectManager->get('Magento\CatalogUrlRewrite\Observer\CategoryProcessUrlRewriteSavingObserver');
        $categoryProcess->execute($observer);
    }

    $offset += BATCH_SIZE;
}


/* REINDEX URL REWRITES FOR PRODUCTS NOT REINDEXED BY CATEGORY REINDEX */

/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $productCollection */
$productCollection = $objectManager->get('Magento\Catalog\Model\ResourceModel\Product\Collection');
/** @var |Magento\Framework\DB\Select $select */
$select = $productCollection->getSelect()
    ->joinLeft(
        ['ur' => $productCollection->getTable('url_rewrite')],
        'ur.entity_id = e.entity_id AND ur.entity_type="product"',
        []
    )->where('ur.entity_id is null');


$offset = 0;
while ($productIds = $productCollection->getAllIds(BATCH_SIZE, $offset)) {
    /** @var \Magento\Catalog\Model\Product $product */
    $product = $objectManager->get('Magento\Catalog\Model\Product');
    $eventName = 'catalog_product_save_after';

    foreach ($productIds as $productId) {
        $productId = (int)$productId;
        $product->load($productId);
        $product->setOrigData('url_key', '');

        $data = ['product' => $product];

        /** @var \Magento\Framework\Event $event */
        $event = new \Magento\Framework\Event($data);
        $event->setName($eventName);

        /** @var \Magento\Framework\Event\Observer $observer */
        $observer = new \Magento\Framework\Event\Observer();
        $observer->setData(array_merge(['event' => $event], $data));

        /** @var \Magento\CatalogUrlRewrite\Observer\ProductProcessUrlRewriteSavingObserver $productProcess */
        $productProcess = $objectManager->get('Magento\CatalogUrlRewrite\Observer\ProductProcessUrlRewriteSavingObserver');
        $productProcess->execute($observer);
    }

    $offset += BATCH_SIZE;
}