0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited apr 4 17

created apr 4 17 Jason C