Created for https://math.meta.stackexchange.com/questions/30743/auto-detecting-dead-links-within-the-stack-exchange-network
Q&A for users of Linux, FreeBSD and other Un*x-like operating systems
-- Known renamed sites, from https://api.stackexchange.com/docs/sites#pagesize=999&filter=!w*vlTok)SMO-Fx9UGp&run=true -- JS code to generate list: $.ajax('https://api.stackexchange.com/2.2/sites?pagesize=999&filter=!w*vlTok)SMO-Fx9UGp').then( json => console.log( json.items.flatMap( item => { let url = item.site_url.replace(/^https?:\/\//, ''); return (item.aliases || []).map( alias => alias.replace(/^https?:\/\//, '') ).filter( alias => !/^(meta\.\w+|\w+\.meta)\.stack(exchange|overflow)\.com$/.test(alias) ).map( alias => ` ('${url}', '${alias}')` ) } ).sort().join(',\n') ) ) CREATE TABLE #SiteAliases ( RealName NVARCHAR(255) NOT NULL, Alias NVARCHAR(255) NOT NULL ) INSERT INTO #SiteAliases VALUES ('3dprinting.stackexchange.com', 'threedprinting.stackexchange.com'), ('academia.stackexchange.com', 'academics.stackexchange.com'), ('alcohol.stackexchange.com', 'beer.stackexchange.com'), ('alcohol.stackexchange.com', 'dranks.stackexchange.com'), ('apple.stackexchange.com', 'askdifferent.com'), ('askubuntu.com', 'ubuntu.stackexchange.com'), ('bicycles.stackexchange.com', 'bicycle.stackexchange.com'), ('bicycles.stackexchange.com', 'bikes.stackexchange.com'), ('bicycles.stackexchange.com', 'cycling.stackexchange.com'), ('boardgames.stackexchange.com', 'boardgame.stackexchange.com'), ('communitybuilding.stackexchange.com', 'moderator.stackexchange.com'), ('communitybuilding.stackexchange.com', 'moderators.stackexchange.com'), ('cooking.meta.stackexchange.com', 'meta.seasonedadvice.com'), ('cooking.stackexchange.com', 'seasonedadvice.com'), ('crypto.stackexchange.com', 'cryptography.stackexchange.com'), ('cs.stackexchange.com', 'computerscience.stackexchange.com'), ('dsp.stackexchange.com', 'signals.stackexchange.com'), ('electronics.stackexchange.com', 'chiphacker.com'), ('electronics.stackexchange.com', 'www.chiphacker.com'), ('english.stackexchange.com', 'elu.stackexchange.com'), ('expatriates.stackexchange.com', 'expats.stackexchange.com'), ('gaming.meta.stackexchange.com', 'meta.arqade.com'), ('gaming.meta.stackexchange.com', 'meta.thearqade.com'), ('gaming.stackexchange.com', 'arqade.com'), ('gaming.stackexchange.com', 'thearqade.com'), ('gardening.stackexchange.com', 'landscaping.stackexchange.com'), ('german.stackexchange.com', 'deutsch.stackexchange.com'), ('graphicdesign.stackexchange.com', 'graphicdesigns.stackexchange.com'), ('graphicdesign.stackexchange.com', 'graphicsdesign.stackexchange.com'), ('homebrew.stackexchange.com', 'brewadvice.com'), ('homebrew.stackexchange.com', 'homebrewing.stackexchange.com'), ('interpersonal.stackexchange.com', 'interpersonalskills.stackexchange.com'), ('ja.stackoverflow.com', 'jp.stackoverflow.com'), ('judaism.stackexchange.com', 'mi.yodeya.com'), ('judaism.stackexchange.com', 'miyodeya.com'), ('judaism.stackexchange.com', 'yodeya.com'), ('judaism.stackexchange.com', 'yodeya.stackexchange.com'), ('linguistics.stackexchange.com', 'linguist.stackexchange.com'), ('math.stackexchange.com', 'mathematics.stackexchange.com'), ('math.stackexchange.com', 'maths.stackexchange.com'), ('mathoverflow.net', 'mathoverflow.com'), ('mathoverflow.net', 'mathoverflow.stackexchange.com'), ('mechanics.stackexchange.com', 'garage.stackexchange.com'), ('medicalsciences.stackexchange.com', 'health.stackexchange.com'), ('money.stackexchange.com', 'basicallymoney.com'), ('money.stackexchange.com', 'www.basicallymoney.com'), ('movies.stackexchange.com', 'tv.stackexchange.com'), ('music.stackexchange.com', 'guitar.stackexchange.com'), ('music.stackexchange.com', 'guitars.stackexchange.com'), ('patents.meta.stackexchange.com', 'meta.askpatents.com'), ('patents.stackexchange.com', 'askpatents.com'), ('patents.stackexchange.com', 'askpatents.stackexchange.com'), ('photo.stackexchange.com', 'photography.stackexchange.com'), ('photo.stackexchange.com', 'photos.stackexchange.com'), ('psychology.stackexchange.com', 'cogsci.stackexchange.com'), ('pt.stackoverflow.com', 'br.stackoverflow.com'), ('pt.stackoverflow.com', 'stackoverflow.com.br'), ('ru.meta.stackoverflow.com', 'meta.hashcode.ru'), ('ru.stackoverflow.com', 'hashcode.ru'), ('ru.stackoverflow.com', 'stackoverflow.ru'), ('rus.stackexchange.com', 'russ.hashcode.ru'), ('rus.stackexchange.com', 'russ.stackexchange.com'), ('scifi.stackexchange.com', 'fantasy.stackexchange.com'), ('scifi.stackexchange.com', 'sciencefiction.stackexchange.com'), ('security.stackexchange.com', 'itsecurity.stackexchange.com'), ('sharepoint.stackexchange.com', 'sharepointoverflow.com'), ('sharepoint.stackexchange.com', 'www.sharepointoverflow.com'), ('skeptics.stackexchange.com', 'skeptic.stackexchange.com'), ('skeptics.stackexchange.com', 'skepticexchange.com'), ('softwareengineering.stackexchange.com', 'programmer.stackexchange.com'), ('softwareengineering.stackexchange.com', 'programmers.stackexchange.com'), ('sound.stackexchange.com', 'socialsounddesign.com'), ('sound.stackexchange.com', 'sounddesign.stackexchange.com'), ('space.stackexchange.com', 'thefinalfrontier.stackexchange.com'), ('spanish.stackexchange.com', 'espanol.stackexchange.com'), ('stackoverflow.com', 'facebook.stackoverflow.com'), ('stackoverflow.com', 'www.stackoverflow.com'), ('stats.stackexchange.com', 'crossvalidated.com'), ('stats.stackexchange.com', 'statistics.stackexchange.com'), ('unix.stackexchange.com', 'linux.stackexchange.com'), ('ux.stackexchange.com', 'ui.stackexchange.com'), ('vegetarianism.stackexchange.com', 'veg.stackexchange.com'), ('vegetarianism.stackexchange.com', 'vegetarian.stackexchange.com'), ('vi.stackexchange.com', 'vim.stackexchange.com'), ('video.stackexchange.com', 'avp.stackexchange.com'), ('webapps.meta.stackexchange.com', 'meta.nothingtoinstall.com'), ('webapps.stackexchange.com', 'nothingtoinstall.com'), ('webmasters.stackexchange.com', 'webmaster.stackexchange.com'), ('writing.stackexchange.com', 'writer.stackexchange.com'), ('writing.stackexchange.com', 'writers.stackexchange.com') -- Add meta site aliases for renamed SE.com / SO.com subdomains: INSERT INTO #SiteAliases SELECT CASE WHEN RealName LIKE '%.stackexchange.com' OR RealName LIKE '%.stackoverflow.com' THEN LEFT(RealName, LEN(RealName) - 17) + 'meta.' + RIGHT(RealName, 17) ELSE 'meta.' + RealName -- for meta.mathoverflow.net and meta.askubuntu.com END AS SiteName, LEFT(Alias, LEN(Alias) - 17) + 'meta.' + RIGHT(Alias, 17) AS Alias FROM #SiteAliases WHERE Alias LIKE '%.stackexchange.com' OR Alias LIKE '%.stackoverflow.com' -- Compile a table of DB name / hostname pairs: CREATE TABLE #RawSiteNames ( DBName NVARCHAR(255) NOT NULL, HostName NVARCHAR(255) NOT NULL ) DECLARE sites CURSOR FOR SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange') DECLARE @dbname NVARCHAR(255), @host NVARCHAR(255) OPEN sites FETCH NEXT FROM sites INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN -- This gets most hostnames right, except that per-site metas end up -- with old pre-HTTPS hostname like meta.whatever.stackexchange.com: SET @host = 'com' SELECT @host = value + '.' + @host FROM STRING_SPLIT(LOWER(@dbname), '.') -- Convert per-site meta hostnames to whatever.meta.stackexchange.com: IF @host LIKE 'meta.%.stackexchange.com' OR @host LIKE 'meta.%.stackoverflow.com' SET @host = SUBSTRING(@host, 6, LEN(@host) - 22) + 'meta.' + RIGHT(@host, 17) INSERT INTO #RawSiteNames (DBName, HostName) VALUES (@dbname, @host) FETCH NEXT FROM sites INTO @dbname END CLOSE sites DEALLOCATE sites -- Apply aliases: SELECT DBName, COALESCE(RealName, HostName) AS HostName INTO #SiteNames FROM #RawSiteNames LEFT JOIN #SiteAliases ON Alias = HostName -- Get current site hostname: DECLARE @hostname NVARCHAR(255) SELECT @hostname = HostName FROM #SiteNames WHERE DBName = DB_NAME() -- Locate posts with potential links to other sites: CREATE TABLE #CandidatePosts ( Id INT NOT NULL, Body NVARCHAR(MAX) NOT NULL ); WITH const AS (SELECT '<a href="http://' AS http, '<a href="https://' AS https, '<a href="//' AS noproto, '<a href="//' + @hostname + '/' AS localpattern, '<a href="//rads.stackoverflow.com/amzn/' AS amazonpattern, '<censored xxx="' AS censored ) INSERT INTO #CandidatePosts SELECT Id, REPLACE(REPLACE(REPLACE(REPLACE(LOWER(Body), http, noproto), https, noproto), localpattern, censored), amazonpattern, censored) AS Body FROM Posts, const WHERE Body LIKE '%<a href="%.[cn][oe][mt]/[qa]%' -- Filter out posts with links only to non-SE sites: DELETE FROM #CandidatePosts WHERE Body NOT LIKE '%<a href="//%' OR (Body NOT LIKE '%.stackexchange.com/[qa]%' AND Body NOT LIKE '%[/.]stackoverflow.com/[qa]%' AND Body NOT LIKE '%[/.]superuser.com/[qa]%' AND Body NOT LIKE '%[/.]serverfault.com/[qa]%' AND Body NOT LIKE '%[/.]mathoverflow.net/[qa]%' AND Body NOT LIKE '%[/.]askubuntu.com/[qa]%' AND Body NOT LIKE '%[/.]stackapps.com/[qa]%' ) -- Extract cross-site from post bodies: CREATE TABLE #CrossSiteLinks ( SourceId INT NOT NULL, HostName NVARCHAR(255) NOT NULL, TargetId INT NOT NULL, ShortcutKey NCHAR(1) NOT NULL ) DECLARE @postid INT, @postbody NVARCHAR(MAX), @segment NVARCHAR(MAX), -- @host NVARCHAR(255), already declared above @path NVARCHAR(255), @qnum NVARCHAR(255), @slug NVARCHAR(255), @anum NVARCHAR(255) DECLARE candidates CURSOR FOR SELECT Id, Body FROM #CandidatePosts OPEN candidates FETCH NEXT FROM candidates INTO @postid, @postbody WHILE @@FETCH_STATUS = 0 BEGIN -- find all links in the post body DECLARE segments CURSOR FOR SELECT value FROM STRING_SPLIT(@postbody, '<') OPEN segments FETCH NEXT FROM segments INTO @segment WHILE @@FETCH_STATUS = 0 BEGIN IF @segment LIKE 'a href="//%"%' BEGIN -- extract hostname and URL path, append a bunch of slashes SET @segment = SUBSTRING(@segment, LEN('a href="//') + 1, LEN(@segment)) SET @segment = LEFT(@segment, PATINDEX('%[#?"]%', @segment) - 1) + '/////' -- extract hostname and path components from URL SET @host = LEFT(@segment, CHARINDEX('/', @segment) - 1) SET @segment = RIGHT(@segment, LEN(@segment) - LEN(@host) - 1) SET @path = LEFT(@segment, CHARINDEX('/', @segment) - 1) SET @segment = RIGHT(@segment, LEN(@segment) - LEN(@path) - 1) SET @qnum = LEFT(@segment, CHARINDEX('/', @segment) - 1) SET @segment = RIGHT(@segment, LEN(@segment) - LEN(@qnum) - 1) SET @slug = LEFT(@segment, CHARINDEX('/', @segment) - 1) SET @segment = RIGHT(@segment, LEN(@segment) - LEN(@slug) - 1) SET @anum = LEFT(@segment, CHARINDEX('/', @segment) - 1) -- check if components look like an SE question/answer link IF @path IN ('q', 'a', 'questions') BEGIN IF LEN(@qnum) BETWEEN 1 AND 9 AND PATINDEX('%[^0-9]%', @qnum) = 0 BEGIN IF @host LIKE '%.stackexchange.com' OR @host LIKE '%.stackoverflow.com' OR EXISTS (SELECT 1 FROM #SiteNames WHERE HostName = @host) OR EXISTS (SELECT 1 FROM #SiteAliases WHERE Alias = @host) BEGIN -- convert meta.whatever.stackexchange.com to whatever.meta.stackexchange.com: IF @host LIKE 'meta.%.stackexchange.com' OR @host LIKE 'meta.%.stackoverflow.com' SET @host = SUBSTRING(@host, 6, LEN(@host) - 22) + 'meta.' + RIGHT(@host, 17) -- canonicalize hostname: SELECT @host = RealName FROM #SiteAliases WHERE Alias = @host -- save link for checking: IF LEN(@anum) BETWEEN 1 AND 9 AND PATINDEX('%[^0-9]%', @anum) = 0 AND @path = 'questions' INSERT INTO #CrossSiteLinks VALUES (@postid, @host, @anum, 'a') ELSE INSERT INTO #CrossSiteLinks VALUES (@postid, @host, @qnum, LEFT(@path, 1)) END END END END FETCH NEXT FROM segments INTO @segment END CLOSE segments DEALLOCATE segments FETCH NEXT FROM candidates INTO @postid, @postbody END CLOSE candidates DEALLOCATE candidates -- Loop over other sites, exclude links to existing posts: SELECT * INTO #FilteredLinks FROM #CrossSiteLinks WHERE HostName NOT IN (SELECT HostName FROM #SiteNames) DECLARE sites CURSOR FOR SELECT DISTINCT DBName, #SiteNames.HostName FROM #SiteNames JOIN #CrossSiteLinks ON #SiteNames.HostName = #CrossSiteLinks.HostName WHERE DBName <> DB_NAME() OPEN sites FETCH NEXT FROM sites INTO @dbname, @hostname WHILE @@FETCH_STATUS = 0 BEGIN SELECT * INTO #CurrentSiteLinks FROM #CrossSiteLinks WHERE HostName = @hostname EXECUTE(' USE [' + @dbname + '] INSERT INTO #FilteredLinks SELECT #CurrentSiteLinks.* FROM #CurrentSiteLinks LEFT JOIN Posts ON Posts.Id = TargetId WHERE Posts.Id IS NULL ') DROP TABLE #CurrentSiteLinks FETCH NEXT FROM sites INTO @dbname, @hostname END CLOSE sites DEALLOCATE sites -- Output results: SELECT DISTINCT 'https://' + HostName + '/' + ShortcutKey + '/' + CAST(TargetId AS NVARCHAR) AS [Target Link], SourceId AS [Post Link], PostTypes.Name AS [Type], Posts.Score AS [Score], CASE WHEN Users.Id IS NULL THEN Posts.OwnerDisplayName ELSE 'site://users/' + CAST(Users.Id AS NVARCHAR) + '|' + Users.DisplayName END AS [Author], Posts.CreationDate AS [Created], Posts.LastEditDate AS [Edited], Posts.ClosedDate AS [Closed] FROM #FilteredLinks JOIN Posts ON Posts.Id = SourceId LEFT JOIN PostTypes ON PostTypes.Id = PostTypeId LEFT JOIN Users ON Users.Id = OwnerUserId