Templates that are not redirects and that have titles ending in '-stub' that are not linked to from any category page that transcludes {{ stub category}}, {{ regional stub category}} or {{ parent-only stub category}}. Links from redirects to the template are counted also. Only those templates with more than 50 transclusions (directly or via redirect) are listed.
Generated by TB ( talk) 14:08, 7 March 2015 (UTC)
Note: Manual update completed February 24, 2022
Mysql transcript to aid in regenerating:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DROP TABLE IF EXISTS rep1;
DROP TABLE IF EXISTS rep2;
-- First, capture all potentially interesting stub templates
CREATE TABLE rep1 AS
SELECT page_title, page_is_redirect
FROM enwiki_p.page
WHERE page_namespace = 10
AND page_title LIKE '%-stub'
AND page_is_redirect = 0;
-- Add in any templates that redirect to one of these.
ALTER TABLE rep1 ADD COLUMN redir varbinary(255);
INSERT INTO rep1
SELECT p.page_title, p.page_is_redirect, r.page_title
FROM enwiki_p.page p
INNER JOIN enwiki_p.redirect ON rd_from = p.page_id
INNER JOIN rep1 r ON rd_namespace = 10 AND rd_title = r.page_title
WHERE p.page_namespace = 10
AND p.page_is_redirect = 1
AND r.page_is_redirect = 0;
-- Now count how many times each is transcluded
alter table rep1 add column trans int(8);
-- Process in chunks - repeat as necessary
UPDATE rep1
SET trans = (
SELECT count(*)
FROM enwiki_p.templatelinks
WHERE tl_namespace = 10
AND tl_title = page_title )
WHERE trans IS NULL
LIMIT 5000;
alter table rep1 add index( redir );
-- Now we need to credit transclusions of redirects to
-- the targets of those redirects
UPDATE rep1 r1
INNER JOIN rep1 r2 ON r1.page_title = r2.redir
SET r1.trans = r1.trans + r2.trans
WHERE r1.page_is_redirect = 0
AND r2.page_is_redirect = 1;
-- Now find categories of interest
CREATE table rep2 AS
SELECT page_id, page_title
FROM enwiki_p.page
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id
WHERE tl_namespace = 10
AND tl_title = "Stub_category"
AND page_namespace = 14;
REPLACE INTO rep2
SELECT page_id, page_title
FROM enwiki_p.page
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id
WHERE tl_namespace = 10
AND tl_title = "Parent-only_stub_category"
AND page_namespace = 14;
REPLACE INTO rep2
SELECT page_id, page_title
FROM enwiki_p.page
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id
WHERE tl_namespace = 10
AND tl_title = "Regional_stub_category"
AND page_namespace = 14;
-- Index these
ALTER TABLE rep2 ADD INDEX( page_id );
ALTER TABLE rep1 ADD INDEX ( page_title );
-- Now count how many time each interesting template is linked from an interesting category
ALTER TABLE rep1 ADD COLUMN cats int(8);
-- Process in chunks - repeat as necessary
UPDATE rep1 r1
SET cats = (
SELECT count(*)
FROM enwiki_p.pagelinks l
INNER JOIN rep2 r2 ON l.pl_from = r2.page_id
WHERE l.pl_namespace = 10
AND l.pl_title = r1.page_title )
WHERE Cats IS NULL
LIMIT 5000;
-- Now we need to credit any category links to redirects to
-- the targets of those redirects
UPDATE rep1 r1
INNER JOIN rep1 r2 ON r1.page_title = r2.redir
SET r1.cats = r1.cats + r2.cats
WHERE r1.page_is_redirect = 0
AND r2.page_is_redirect = 1;
-- Test the results
SELECT count(*) FROM rep1 WHERE page_is_redirect = 0 AND cats = 0 AND trans >= 60;
-- Generate some output
SELECT CONCAT( '*{{tl|', rep1.page_title, '}} - ', trans, ' transclusions' )
FROM rep1
WHERE page_is_redirect = 0
AND cats = 0
AND trans >= 50;