This query lists questions that have been closed as duplicates of another question that has since been deleted. Such questions should be either deleted or, if they have good answers or are otherwise of decent quality, reopened. Note that non-locked questions with no answers and a negative score will be automatically deleted by the RemoveDeadQuestions script after 30 days, as indicated by the "Roombable?" column. By default, the results will be sorted with the most recently closed questions first. You can click the column headings to change the sort order. For example, questions with a high answer score may be good candidates for reopening (or for a historical lock), while questions with a high score but no answers may simply need a new dupe target (see http://meta.stackexchange.com/q/291824).
Q&A about the site for those who base their lives on Jewish law and tradition and anyone interested in learning more
-- step 1: find dupes with deleted targets CREATE TABLE #DeletedDupes ( PostId INT NOT NULL, TargetId INT NULL, MaxAnswerScore INT NULL, MaxAnswerLength INT NULL, LockEventId INT NULL ) INSERT INTO #DeletedDupes SELECT BrokenLink.PostId as PostId, MAX(BrokenLink.RelatedPostId) as TargetId, MAX(Answer.Score) as MaxAnswerScore, MAX(LEN(Answer.Body)) as MaxAnswerLength, MAX(LockEvent.Id) as LockEventId FROM PostLinks as BrokenLink JOIN Posts as SourcePost ON SourcePost.Id = BrokenLink.PostId LEFT JOIN Posts as TargetPost ON TargetPost.Id = BrokenLink.RelatedPostId LEFT JOIN Posts as Answer ON Answer.PostTypeId = 2 AND Answer.ParentId = BrokenLink.PostId LEFT JOIN PostHistory as LockEvent ON LockEvent.PostHistoryTypeId IN (14, 15) AND LockEvent.PostId = BrokenLink.PostId WHERE BrokenLink.LinkTypeId = 3 AND TargetPost.Id IS NULL AND NOT EXISTS ( SELECT 1 FROM PostLinks as OtherLink JOIN Posts as OtherTarget ON OtherTarget.Id = OtherLink.RelatedPostId WHERE OtherLink.LinkTypeId = 3 AND OtherLink.PostId = BrokenLink.PostId ) GROUP BY BrokenLink.PostId -- step 2: determine current site host name for migration matching DECLARE @sitename NVARCHAR(MAX) = DB_NAME() SET @sitename = REPLACE(@sitename, 'StackExchange.Mathoverflow', 'Mathoverflow') SET @sitename = REPLACE(@sitename, 'StackExchange.Ubuntu', 'AskUbuntu') SET @sitename = REPLACE(@sitename, 'StackExchange.Audio', 'StackExchange.Video') SET @sitename = REPLACE(@sitename, 'StackExchange.Garage', 'StackExchange.Mechanics') SET @sitename = REPLACE(@sitename, 'StackExchange.Moderators', 'StackExchange.CommunityBuilding') SET @sitename = REPLACE(@sitename, 'StackExchange.Photography', 'StackExchange.Photo') SET @sitename = REPLACE(@sitename, 'StackExchange.Programmers', 'StackExchange.SoftwareEngineering') SET @sitename = REPLACE(@sitename, 'StackOverflow.Br', 'StackOverflow.Pt') DECLARE @tld NVARCHAR(3) = (CASE WHEN @sitename LIKE 'Mathoverflow%' THEN 'net' ELSE 'com' END) -- StackExchange.Whatever.Meta -> https://whatever.meta.stackexchange.com DECLARE @newurlprefix NVARCHAR(MAX) = 'https://' + LOWER(CASE WHEN CHARINDEX('.', @sitename, 0) = 0 THEN @sitename ELSE RIGHT(@sitename, LEN(@sitename) - CHARINDEX('.', @sitename, 0)) + '.' + LEFT(@sitename, CHARINDEX('.', @sitename, 0) - 1) END) + '.' + @tld -- StackExchange.Whatever.Meta -> http://meta.whatever.stackexchange.com DECLARE @oldurlprefix NVARCHAR(MAX) = '' SELECT @oldurlprefix = CONCAT(value, '.', @oldurlprefix) FROM STRING_SPLIT(@sitename, '.') SET @oldurlprefix = 'http://' + LOWER(@oldurlprefix) + @tld -- step 3: exclude duplicates of migrated posts DECLARE sites CURSOR FOR SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange', DB_NAME()) DECLARE @sitedbname AS nvarchar(max) DECLARE @query AS nvarchar(max) OPEN sites FETCH NEXT FROM sites INTO @sitedbname WHILE @@FETCH_STATUS = 0 BEGIN SET @query = ' USE [' + @sitedbname + '] DELETE FROM #DeletedDupes WHERE TargetId IN ( SELECT CAST(LEFT(Path, PATINDEX(''%[^0-9]%'', Path) - 1) as INT) FROM ( SELECT PostId, SUBSTRING(Comment, PATINDEX(''%[0-9]%'', Comment), LEN(Comment)) as Path FROM PostHistory JOIN Posts ON Posts.Id = PostId AND PostTypeId = 1 WHERE PostHistoryTypeId = 36 AND Comment LIKE ''from ' + @oldurlprefix + '/questions/%'' UNION ALL SELECT PostId, SUBSTRING(Comment, PATINDEX(''%[0-9]%'', Comment), LEN(Comment)) as Path FROM PostHistory JOIN Posts ON Posts.Id = PostId AND PostTypeId = 1 WHERE PostHistoryTypeId = 36 AND Comment LIKE ''from ' + @newurlprefix + '/questions/%'' ) as X )' EXECUTE(@query) FETCH NEXT FROM sites INTO @sitedbname END CLOSE sites DEALLOCATE sites -- step 4: format results nicely SELECT BrokenLink.PostId as [Post Link], SourcePost.ViewCount as Views, SourcePost.Score, SourcePost.AnswerCount as Answers, BrokenLink.MaxAnswerScore as AScore, BrokenLink.MaxAnswerLength as ALen, (CASE WHEN SourcePost.AcceptedAnswerId IS NULL THEN '' ELSE 'Accepted' END) as Accepted, SourcePost.ClosedDate as Closed, TargetPost.DeletionDate as Deleted, (CASE WHEN LockEventId IS NOT NULL AND (SELECT PostHistoryTypeId FROM PostHistory WHERE Id = LockEventId) = 14 THEN 'No (Post Locked)' WHEN SourcePost.AnswerCount = 1 THEN 'No (1 answer)' WHEN SourcePost.AnswerCount > 1 THEN 'No (' + Cast(SourcePost.AnswerCount as VARCHAR) + ' answers)' WHEN SourcePost.Score = 0 THEN 'If downvoted' WHEN SourcePost.Score > 0 THEN 'If downvoted x' + Cast(SourcePost.Score+1 as VARCHAR) ELSE 'Yes, on ' + Convert(VARCHAR(10), DateAdd(Day, 30, SourcePost.CreationDate), 126) END) as [Roombable?], SourcePost.Tags as [Tags], -- Questions closed before 2013-02-06 have their duplicate link banner -- edited into the post text by the Community user. Sometimes later edits -- may have removed or modified the banner. (CASE WHEN SourcePost.ClosedDate >= '2013-02-06' AND CommunityEdit.Id IS NULL THEN 'Auto' WHEN SourcePost.Body LIKE '<blockquote>%/questions/' + Cast(TargetId as NVARCHAR) + '/%</blockquote>%' THEN 'In body' WHEN SourcePost.Body LIKE '<blockquote>%' THEN 'Modified' ELSE 'Missing' END) as [Banner], TargetPost.Id as [Target ID], SourcePost.Title as [Title] -- for sorting FROM #DeletedDupes as BrokenLink JOIN Posts as SourcePost ON SourcePost.Id = BrokenLink.PostId LEFT JOIN PostsWithDeleted as TargetPost ON TargetPost.Id = BrokenLink.TargetId LEFT JOIN PostHistory as CommunityEdit ON CommunityEdit.PostId = BrokenLink.PostId AND CommunityEdit.UserId = -1 AND CommunityEdit.PostHistoryTypeId = 5 AND CommunityEdit.Comment = 'insert duplicate link' AND CommunityEdit.CreationDate >= DateAdd(Second, -5, SourcePost.ClosedDate) AND CommunityEdit.CreationDate <= DateAdd(Second, +5, SourcePost.ClosedDate) ORDER BY TargetPost.DeletionDate DESC