~Bad links from Commons to other wikis

SQL

#USE commonswiki_p;
USE dewiki_p;
#USE dewikisource_p;
#USE dewikivoyage_p;
#USE dewiktionary_p;
#USE enwiki_p;
#USE eswiki_p;
#USE frwiki_p;
#USE itwiki_p;
#USE nlwiki_p;
#USE jawiki_p;
SET @w = 'dewiki'; SET @pr = 'de'; SET @prs = @pr;
SET @l = (SELECT lang FROM meta_p.wiki WHERE dbname = CONCAT(@w, '_p'));
#SET @w = LEFT (DATABASE(), LENGTH(DATABASE()) - 2); SET @l = (SELECT lang FROM meta_p.wiki WHERE dbname = @w);
#SET @pr = IF (@w IN ('metawiki', 'specieswiki'), LEFT(@w, LENGTH(@w) - 4), @l);
SELECT CONCAT(now(), @w, @pr, @l);
#empty links, no target given
(SELECT CONCAT('# [[', IF (cp.page_namespace = 14, ':Category:', ''), REPLACE (cp.page_title, '_', ' '), ']] ',
               '→ [[:', @pr, ':', REPLACE (cl.iwl_title, '_', ' '), ']]') AS wikipage
FROM commonswiki_p.iwlinks cl
INNER JOIN commonswiki_p.page cp
ON cp.page_id = cl.iwl_from
AND cp.page_namespace IN (0, 14)
AND cp.page_title NOT REGEXP '(?:^Main_Page|^Wikipedia\/)'
WHERE cl.iwl_prefix = @pr
AND cl.iwl_title REGEXP '^ *$'
)/*
UNION
#broken interwikilinks
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]') AS wikipage
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace IN (0, 14)
AND wp.page_is_redirect = 0
AND wp.page_title NOT REGEXP @p
WHERE iwl_prefix ='commons'
AND iwl_title LIKE 'Category:%'
AND NOT EXISTS (SELECT 1 FROM commonswiki_p.page cp
                WHERE cp.page_title = SUBSTRING(iwl_title, 10)
                AND cp.page_namespace = 14)
)
UNION
#broken external interwiki links
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '→ [[:c:', REPLACE ((SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), 36)), '_', ' '), ']]') AS wikipage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
AND wp.page_is_redirect = 0
AND wp.page_title NOT REGEXP @p
WHERE el_to LIKE 'https://commons.wikimedia.org/wiki/Category:%'
AND el_to NOT LIKE '%\%%' #temp
AND NOT EXISTS (SELECT 1 FROM commonswiki_p.page cp
                WHERE cp.page_namespace = 14
                AND cp.page_title = SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), 45)
               )
)*/
#ORDER BY wikipage
LIMIT 1000;
SQL

Checking query status...
