Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles
-- Displays top 500 comments, network wide, with clickable links. -- -- http://stackexchange.com/users/305991/jason-c CREATE PROCEDURE #to_url @DB NVARCHAR(MAX), @URL NVARCHAR(MAX) OUT AS BEGIN SELECT @URL = CONCAT(value, '.', @URL) FROM STRING_SPLIT(@DB, '.') SET @URL = CONCAT(LOWER(@URL), 'com') SELECT @URL = CASE @URL WHEN 'meta.mathoverflow.stackexchange.com' THEN 'meta.mathoverflow.stackexchange.net' WHEN 'audio.stackexchange.com' THEN 'avp.stackexchange.com' WHEN 'meta.audio.stackexchange.com' THEN 'meta.avp.stackexchange.com' ELSE @URL END SET @URL = CONCAT('http://', @URL, '/posts/comments/') END; GO DECLARE @db SYSNAME DECLARE @Query NVARCHAR(max) DECLARE c CURSOR FOR SELECT Name FROM sys.databases WHERE database_id > 5 CREATE TABLE #Results ([Site] NVARCHAR(MAX), [Link] NVARCHAR(MAX), [Id] INT, [Score] INT, [Text] NVARCHAR(MAX)) OPEN c FETCH NEXT FROM c INTO @db WHILE (@@FETCH_STATUS = 0) BEGIN SET @Query = 'DECLARE @url NVARCHAR(MAX) EXEC #to_url ''' + @db + ''', @url OUT; INSERT INTO #Results SELECT TOP 500 ''' + @db + ''' [Site], CONCAT(@url, c.Id), c.Id, c.Score, c.Text FROM ' + QUOTENAME(@db) + '.dbo.Comments c ORDER BY c.Score DESC' EXEC(@Query) FETCH NEXT FROM c INTO @db END; CLOSE c; DEALLOCATE c; SELECT TOP 500 * FROM #Results ORDER BY Score DESC