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.
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,
max(PostHistory.CreationDate) as Last
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)],
Last as [Latest Migration]
WHERE Count > 0
ORDER BY Count desc, Site;
select row_number() over (order by Count desc), Count
where Count > 0