Find commons pages unable to access templates after SUL user renames

Query by User:Bawolff. It finds pages which are using user namespace templates which were recently renamed (without redirects) by [[User:Keegan (WMF)]] as part of  SUL finalization.  See https://commons.wikimedia.org/wiki/Commons:Village_pump/Archive/2015/04#230k_SUL_user_renames_by_User:Maintenance_script for discussion

SQL

use commonswiki_p;

select p1.page_namespace, p1.page_title, tl_title 
from templatelinks 
  inner join page p1 
    on tl_from = p1.page_id       -- p1 will transclude moved template 
  left outer join page p2 
    on (p2.page_namespace = 2     -- user namespace template
    and p2.page_title = tl_title) 
  inner join logging_logindex 
    on log_namespace = 2 
    and log_title = tl_title      
    and log_type = "move"         -- user template was moved
    and log_params like "%~%"     -- new user template name has a "~"
where tl_namespace = 2            -- user namespace template
and tl_from_namespace in ( 0, 1, 4, 6, 7) 
and p2.page_title is null;

SQL

Checking query status...