Interwiki file links from wikis to Commons

SQL

#links from dewiki to redirecting pages on Commons
USE dewiki_p;
SELECT now() AS timestamp;
#interwiki links
(SELECT CONCAT ('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
        CONCAT ('→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]') AS commonscat
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace = 0
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(iwl_title, 10)
AND cp.page_namespace = 0
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
WHERE iwl_prefix ='commons'
AND iwl_title NOT LIKE '%:%'
AND cp.page_is_redirect = 1
)/*
UNION
#external interwiki links
(SELECT CONCAT('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage,
        CONCAT('→ [[:c:', REPLACE (cp.page_title, '_', ' '), ']]') AS commonscat
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace = 0
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(REPLACE (el_to, '?uselang=de', ''), 45)
AND cp.page_namespace = 0
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
WHERE el_to LIKE 'https://commons.wikimedia.org/wiki/%'
AND cp.page_is_redirect = 1
)*/
#ORDER BY wpage
LIMIT 100;
SQL

Checking query status...
