Find all questions sucessfully migrated away from a site

1

Please login or register to vote for this query.

(click on this box to dismiss)

Loop through the databases for all sites to find posts that were migrated away from the site you specify, and were not closed on the new site. It doesn't matter which site you run this on at data.stackexchange.com. Give a (unique) portion of the site URL as a parameter.

English

Q&A for linguists, etymologists, and serious English language enthusiasts

-- Once a question has been migrated away, the question on the original site is
-- deleted after 30 days and can no longer be found on data.SE. This query searches
-- every other site for these migrated questions, and only matches questions that
-- have never been closed.

-- Bugs: Links for some of the queries in the results are broken
-- change audio to avp, garage to mechanics
-- each language-specific Stack Overflow link is wrong

DECLARE @site NVARCHAR(255) = ##Site:string?scifi##
DECLARE @command NVARCHAR(1024) = 'USE [?];
IF ''?'' NOT LIKE ''%meta%''
AND ''?'' NOT LIKE ''%' + @site + '%''
AND ''?'' <> ''StackApps''
AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''Posts'')
INSERT INTO #MigratedPosts
SELECT replace(''?'', ''StackExchange.'', '''') AS Site,
       count(PostId),
       max(PostHistory.CreationDate) as Last
FROM dbo.PostHistory
LEFT JOIN dbo.Posts on PostHistory.PostId = Posts.Id
WHERE Posts.PostTypeId = 1
      and PostHistoryTypeId = 36
      and Comment like ''from %' + @site + '%''' +
  CASE WHEN ##AllowClosed:int?0## = 0 THEN ' and Posts.ClosedDate is null'
  ELSE '' END + ';'

CREATE TABLE #MigratedPosts (Site NVARCHAR(255), Count INT, Last DATETIME);
EXEC sp_msforeachdb @command

SELECT 'http://data.stackexchange.com/' + lower(Site) + '/query/471982/?site='
       + @Site + '|' + Site as [Site (link to list of questions)],
       Count,
       Last as [Latest Migration]
FROM #MigratedPosts
WHERE Count > 0
ORDER BY Count desc, Site;

select row_number() over (order by Count desc), Count
from #MigratedPosts
where Count > 0

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results