List netvolution duplicates

NetvolutionDuplicates.sql

WITH temp_Pages (pId, pLangId,pParentId, pFriendlyUrl) AS
(
SELECT pId, pLangId,
    pParentId, 
    CONVERT(NVARCHAR(200),
        'http://' + 
        (SELECT e.siteName FROM cms_Sites e WHERE e.siteID=pSiteID) + 
        '/' + 
        (SELECT c.langShortName FROM cms_Languages c WHERE c.langID=pLangID) + 
        '/' + 
        pFriendlyUrl)
FROM cms_Pages WHERE pParentId = 0 AND pStatus='published'
UNION ALL 
SELECT b.pId, b.pLangId,b.pParentId,CONVERT(NVARCHAR(200),a.pFriendlyUrl+'/'+b.pFriendlyUrl)
FROM temp_Pages AS a, cms_Pages AS b
WHERE a.pId = b.pParentId AND a.pLangId = b.pLangId AND b.pStatus='published'
)
SELECT pId, pFriendlyUrl
INTO #AllNetvolutionUrls
FROM temp_Pages
 
 
SELECT * FROM #AllNetvolutionUrls
WHERE pFriendlyUrl IN (
SELECT pFriendlyUrl FROM #AllNetvolutionUrls
GROUP BY pFriendlyUrl
HAVING COUNT(*)>1);
 
DROP TABLE #AllNetvolutionUrls