Q&A for professional and enthusiast programmers
-- Temporary table to store the results CREATE TABLE #results ( Site NVARCHAR(250), PostId INT, RevisionGUID UNIQUEIDENTIFIER, Title NVARCHAR(250), EditDate DATETIME, Summary NVARCHAR(400) ); -- We'll need (at least) one INSERT statement for each site, they'll end up in this variable DECLARE @sql NVARCHAR(MAX) = ''; -- 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, ph.RevisionGUID, COALESCE(p.Title, q.Title), ph.CreationDate, ph.Comment FROM ' + quotename(name) + '.dbo.Users AS u INNER JOIN ' + quotename(name) + '.dbo.PostHistory AS ph ON u.Id = ph.UserId INNER JOIN ' + quotename(name) + '.dbo.Posts AS p ON p.Id = ph.PostId LEFT OUTER JOIN ' + quotename(name) + '.dbo.Posts AS q ON q.Id = p.ParentId WHERE u.AccountId = 6085540 AND ph.PostHistoryTypeId = 5 -- Body edit AND ph.CreationDate >= ''2018-12-25'' AND (ph.Comment LIKE ''%broken %'' OR ph.Comment LIKE ''% roto%'' OR ph.Comment LIKE ''% quebrado%'' OR ph.Comment LIKE ''% неработающ%'') AND ph.Comment NOT LIKE ''%removed%'' ' FROM sys.databases WHERE database_id > 5; -- that's where the site databases begin -- 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 s.SiteName AS 'Site', CONCAT(s.URL, '/posts/', r.PostId, '/revisions#rev-body-', LOWER(r.RevisionGUID), '|', COALESCE(r.Title, 'link')) AS Link, EditDate AS 'Date', Summary AS 'Edit summary' FROM #results AS r INNER JOIN #Sites AS s ON r.Site = s.DatabaseName ORDER BY EditDate DESC; DROP TABLE #results;