3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

-- Displays top 500 comments, network wide, with clickable links.
--
--   AccountId = Your network account ID. Get it from your profile link
--               at http://stackexchange.com.
--
-- 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),
                       [When] DATETIME,
                       [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
      ''' + @db + ''' [Site],
      CONCAT(@url, c.Id),
      c.CreationDate,
      c.Id,
      c.Score,
      c.Text
    FROM
      ' + QUOTENAME(@db) + '.dbo.Comments c
      INNER JOIN ' + QUOTENAME(@db) + '.dbo.Users u on u.Id = c.UserId
    WHERE u.AccountId = ##AccountId##'
  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 25 17

created apr 4 17 anonymous