USE dewiki_p; 

-- enwiki_p, cebwiki_p, svwiki_p, dewiki_p, nlwiki_p

-- frwiki_p, ruwiki_p, itwiki_p, eswiki_p, warwiki_p

-- plwiki_p, viwiki_p, jawiki_p, ptwiki_p, zhwiki_p

-- ukwiki_p, fawiki_p, cawiki_p, arwiki_p

​

-- SELECT COUNT(p.page_id) AS cnt FROM page p JOIN page_props pp ON p.page_id=pp.pp_page WHERE p.page_namespace=0 AND pp.pp_propname='wikibase_item';

SELECT 

    p.page_id AS redirect_id,

    p.page_title AS redirect_title,

    pp.pp_value AS redirect_qid,

    p2.page_id AS target_id,

    r.rd_title AS target_title,

    pp2.pp_value AS target_qid

FROM 

    page p JOIN page_props pp ON p.page_id=pp.pp_page 

           JOIN redirect r ON p.page_id=r.rd_from

           JOIN page p2 ON r.rd_title=p2.page_title

           LEFT JOIN page_props pp2 ON p2.page_id=pp2.pp_page

WHERE 

    p.page_is_redirect=1 

AND p.page_namespace=0 

AND pp.pp_propname='wikibase_item' 

AND pp2.pp_propname='wikibase_item'

AND pp2.pp_value NOT LIKE 'x%';

​