Q&A for those who study, teach, research and apply economics and econometrics
-- Temporary table to store the results 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) ); -- We'll need (at least) one INSERT statement for each site, they'll end up in this variable DECLARE @sql NVARCHAR(MAX) = ''; -- Auxiliary variables 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. -- Generate INSERT statements: -- - Use the database's `name` to indicate the site (this can be converted to site name later on) -- - Fully qualify table names SELECT @sql = @sql + '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 + '; INSERT INTO #results' + N' 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 -- that's where the site databases begin AND name NOT LIKE '%_Temp'; -- these are used during the weekly SEDE refresh -- Execute SQL with INSERT statements 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; -- Present the results by joining them with the sites table SELECT SiteName AS 'site', CONCAT(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, CONCAT(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 temporary tables DROP TABLE #results; DROP TABLE #sites;