User:Kotepho/reports/Media used in namespaces other than 0 that contain a template on the image description page that is in category:Non-free image copyright tags
Appearance
Generated with:
select tl.tl_title, il.il_to, count(il_page.page_id) INTO OUTFILE '/tmp/non-free_images.txt' from categorylinks as cl INNER JOIN page as cl_page ON cl.cl_from = cl_page.page_id INNER JOIN templatelinks as tl ON cl_page.page_title = tl.tl_title AND cl_page.page_namespace = tl.tl_namespace INNER JOIN page as tl_page ON tl.tl_from = tl_page.page_id INNER JOIN imagelinks as il ON tl_page.page_title = il.il_to INNER JOIN page as il_page ON il.il_from = il_page.page_id WHERE cl.cl_to = 'Non-free_image_copyright_tags' AND il_page.page_namespace <> 0 AND tl_page.page_namespace = 6 GROUP BY il.il_to ORDER BY tl.tl_title;
and a little bit of perl to split into pages/tables.
#!/usr/bin/perl @lines = <>; print "[[Template:$1]]\n\n" if $lines[0] =~ m/([^ ]+) ([^ ]+) (\d+)/; print "{| class=\"wikitable\"\ ! Image !! Count\ "; foreach (@lines) { s/([^ ]+) ([^ ]+) (\d+)/|- | [[:Image:$2]] || $3 /; print; } print "|}\n";