Networkwide search template

1

Please login or register to vote for this query.

(click on this box to dismiss)

Monero Meta

Q&A about the site for developers and users of the secure, private and untraceable cryptocurrency Monero

CREATE TABLE #results (site NVARCHAR(250),
                       postid INT,
                       questionid INT,
                       commentid INT,
                       body NVARCHAR(MAX),
                       type NVARCHAR(50),
                       score INT,
                       tags NVARCHAR(250),
                       userid INT,
                       displayname NVARCHAR(40),
                       creationdate DATETIME,
                       title NVARCHAR(250));
                       
DECLARE @sql NVARCHAR(MAX) = '';

DECLARE @filter NVARCHAR(MAX) = '
  WHERE u.AccountId = ' + CAST(##AccountId:int## as nvarchar) + '
    AND COALESCE(c.Text, p.Body)
        COLLATE SQL_Latin1_General_CP1_CI_AS
        LIKE ''%' + ##query:string## + '%'' 
';
-- 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.

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''', p.Id,
  CASE p.PostTypeId WHEN 1 THEN p.Id WHEN 2 THEN p.ParentId ELSE NULL END,
  NULL, p.Body,
  CASE p.PostTypeId WHEN 1 THEN ''Question'' WHEN 2 THEN ''Answer'' ELSE ''PostType = '' + CAST(p.PostTypeId AS NVARCHAR) END,
  p.Score, COALESCE(p.Tags, q.Tags),
  p.OwnerUserId, u.DisplayName,
  p.CreationDate, COALESCE(p.Title, q.Title)
  FROM ' + QUOTENAME(name) + '.dbo.Posts AS p  
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Users AS u
    ON p.OwnerUserId = u.Id
  LEFT OUTER JOIN ' + QUOTENAME(name) + '.dbo.Posts AS q
    ON p.ParentId = q.Id
  LEFT OUTER JOIN ' + QUOTENAME(name) + '.dbo.Comments AS c
    ON 1 = 2 -- Dummy join to make @filter uniform
' + @filter + 'UNION ALL
SELECT ''' + name + ''', p.Id,
  CASE p.PostTypeId WHEN 1 THEN p.Id WHEN 2 THEN p.ParentId ELSE NULL END,
  c.Id, c.Text,
  ''Comment'',
  c.Score, COALESCE(p.Tags, q.Tags),
  c.UserId, u.DisplayName,
  c.CreationDate, COALESCE(p.Title, q.Title)
  FROM ' + QUOTENAME(name) + '.dbo.Comments AS c  
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Users AS u
    ON c.UserId = u.Id
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Posts AS p
    ON c.PostId = p.Id
  LEFT OUTER JOIN ' + QUOTENAME(name) + '.dbo.Posts AS q
    ON p.ParentId = q.Id
' + @filter
FROM sys.databases
WHERE database_id > 5

-- 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'
            )
          )
        )
      )
      ))
  + (CASE WHEN type = 'Comment' THEN '/posts/comments/' + CAST(commentid AS NVARCHAR)
          ELSE '/q/' + CAST(postid AS NVARCHAR) END)
  + '|' + COALESCE(title, '[LINK]') AS 'link',
  body, type, score, tags, creationdate,
  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'
            )
          )
        )
      )
      )) + '/u/' + CAST(userid AS NVARCHAR) + '|' + COALESCE(displayname, '[USER]') AS 'author'
  FROM #results
  ORDER BY creationdate DESC;
     
DROP TABLE #results

Enter Parameters

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