SELECT COUNT(*)

AS "Total cross-namespace redirects from user to draft"

FROM redirect r

INNER JOIN page p ON p.page_id = r.rd_from

WHERE r.rd_namespace = 118

AND   p.page_namespace = 2;

​

SELECT concat( "[https://en.wikipedia.org/w/index.php?title=User:{{urlencode:",

              p.page_title, "}}&redirect=no User:", p.page_title, "] redirects to [[Draft:", r.rd_title, "]]" )

AS "Redirects from user, except user subpages, to draft"

FROM redirect r

INNER JOIN page p ON p.page_id = r.rd_from

WHERE p.page_namespace = 2

AND   p.page_title NOT LIKE '%/%'

AND   r.rd_namespace = 118

ORDER by page_title;

​

SELECT concat( "[https://en.wikipedia.org/w/index.php?title=User:{{urlencode:",

              p.page_title, "}}&redirect=no User:", p.page_title, "] redirects to [[Draft:", r.rd_title, "]]" )

AS "Redirects from user subpage to draft"

FROM redirect r

INNER JOIN page p ON p.page_id = r.rd_from

WHERE p.page_namespace = 2

AND   p.page_title LIKE '%/%'

AND   r.rd_namespace = 118

ORDER by page_title;