Cross-site infavorites:mine search

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- cf. https://meta.stackexchange.com/q/315037/295232
CREATE TABLE #results (site NVARCHAR(250),
                       questionid INT,
                       body NVARCHAR(MAX),
                       score INT,
                       tags NVARCHAR(250),
                       creationdate DATETIME,
                       title NVARCHAR(250));
                       
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''', p.Id, p.Body, p.Score, p.Tags, p.CreationDate, p.Title
  FROM ' + QUOTENAME(name) + '.dbo.Posts AS p  
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Votes AS v
    ON v.PostId = p.Id
   AND v.VoteTypeId = 5 -- Bookmark
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Users AS u
    ON v.UserId = u.Id
  WHERE u.AccountId = ' + CAST(##AccountId:int## as nvarchar) + '
    AND (p.Body LIKE ''%' + ##query:string## + '%'' COLLATE SQL_Latin1_General_CP1_CI_AS
     OR p.Title LIKE ''%' + ##query:string## + '%'' COLLATE SQL_Latin1_General_CP1_CI_AS)
'
FROM sys.databases
WHERE database_id > 5
-- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID.

-- execute it
EXEC (@sql)

SELECT site,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      ))
  + '/q/' + CAST(questionid AS NVARCHAR)
  + '|' + title AS 'link',
  body, score, tags, creationdate
  FROM #results
  ORDER BY creationdate DESC;
     
DROP TABLE #results

Enter Parameters

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