Networkwide search template (main & meta, specific user)

2

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

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)

-- Site details, cf. https://meta.stackexchange.com/a/359795/295232
CREATE TABLE #Sites (URL NVARCHAR(100), SiteName NVARCHAR(100), DatabaseName NVARCHAR(50));
-- fetch Markdown for post containing table with site details
DECLARE @md NVARCHAR(MAX);
SELECT TOP 1 @md = Text
  FROM [StackExchange.Meta].dbo.PostHistory
  WHERE PostId = 359796 AND PostHistoryTypeId = 5
  ORDER BY CreationDate DESC;
-- split Markdown on pipe symbol
CREATE TABLE #TableEntries (ID INT IDENTITY(1, 1), Value NVARCHAR(100));
INSERT INTO #TableEntries (Value)
SELECT LEFT(TRIM(VALUE), 100) FROM STRING_SPLIT(@md, '|')
  WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0;
-- 'pivot' - we have to discard the introductory text, headers and horizontal lines
INSERT INTO #Sites
SELECT t0.Value, t1.Value, t2.Value
  FROM #TableEntries AS t0
  INNER JOIN #TableEntries AS t1
    ON t0.Id + 1 = t1.Id
  INNER JOIN #TableEntries AS t2
    ON t0.Id + 2 = t2.Id
  WHERE t0.Id >= 8 -- that's where the real content starts
    AND t0.Id % 3 = 2;

SELECT SiteName AS 'site',
  URL + (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,
  URL + '/u/' + CAST(userid AS NVARCHAR) + '|' + COALESCE(displayname, '[USER]') AS 'author'
  FROM #Results AS r
  INNER JOIN #Sites AS s
    ON r.Site = s.DatabaseName
  ORDER BY creationdate DESC;
     
DROP TABLE #results

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results