1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

DECLARE @result TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  CommentId INT NOT NULL,
  PostId INT NOT NULL,
  PostTitle VARCHAR(MAX),
  Text VARCHAR(MAX) NOT NULL,
  Score INT NOT NULL,
  CreationDate DATETIME NOT NULL
)

DECLARE @query VARCHAR(MAX) = '';
DECLARE @dbName VARCHAR(MAX);

DECLARE dbCursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE
  name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange');

OPEN dbCursor;
FETCH NEXT FROM dbcursor INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query <> ''
    SET @query = @query + '
      UNION ALL
    '
  
  SET @query = @query + '
    SELECT
      ''' + @dbName + ''' AS DbName,
      Comments.Id AS CommentId,
      Posts.Id AS PostId,
      COALESCE(Posts.Title, Parent.Title) AS PostTitle,
      Comments.Text,
      Comments.Score,
      Comments.CreationDate
    FROM [' + @dbName + '].dbo.Users
    LEFT JOIN [' + @dbName + '].dbo.Comments
      ON Comments.UserId = Users.Id
    LEFT JOIN [' + @dbName + '].dbo.Posts
      ON Posts.Id = Comments.PostId
    LEFT JOIN [' + @dbName + '].dbo.Posts Parent
      ON Parent.Id = Posts.ParentId
    WHERE
      Users.AccountId = ' + CAST(##SEAccountId:int## AS VARCHAR(MAX)) + '
      AND
      PostId IS NOT NULL
    ';

  FETCH NEXT FROM dbCursor INTO @dbName;
END

CLOSE dbCursor;

PRINT @query;

INSERT INTO @result EXEC (@query);

WITH SiteToDb AS (
  SELECT
    name AS DbName,
    LOWER(CASE
      WHEN
        name = 'StackExchange.Programmers'
      THEN
        'softwareengineering.stackexchange.com'
      WHEN
        name = 'StackExchange.Programmers.Meta'
      THEN
        'softwareengineering.meta.stackexchange.com'
      WHEN
        name LIKE 'StackExchange.%'
      THEN
        RIGHT(name, LEN(name) - 14) + '.stackexchange.com'
      WHEN
        name = 'StackOverflow.Br'
      THEN
        'pt.stackoverflow.com'
      WHEN
        name = 'StackOverflow.Br.Meta'
      THEN
        'pt.meta.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__'
      THEN
        RIGHT(name, 2) + '.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__.Meta'
      THEN
        RIGHT(name, 7) + '.stackoverflow.com'
      WHEN
        name = 'StackOverflow'
      THEN
        'stackoverflow.com'
      WHEN
        name = 'StackOverflow.Meta'
      THEN
        'meta.stackoverflow.com'
      WHEN
        name = 'StackApps'
      THEN
        'stackapps.com'
      WHEN
        name = 'ServerFault'
      THEN
        'serverfault.com'
      WHEN
        name = 'ServerFault.Meta'
      THEN
        'meta.serverfault.com'
      WHEN
        name = 'SuperUser'
      THEN
        'superuser.com'
      WHEN
        name = 'SuperUser.Meta'
      THEN
        'meta.superuser.com'
      ELSE
        NULL
      END
    ) AS SiteName
  FROM sysdatabases
  WHERE
    name NOT IN (
      'master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange'
    )
)
SELECT
  'https://' + SiteToDb.SiteName + '/q/'
    + CAST(Result.PostId AS VARCHAR(MAX)) + '#comment'
    + CAST(Result.CommentId AS VARCHAR(MAX)) + '_'
    + CAST(Result.PostId AS VARCHAR(MAX)) + '|' +
    CASE
      WHEN Result.PostTitle IS NOT NULL THEN Result.PostTitle
      ELSE CAST(Result.PostId AS VARCHAR(MAX))
    END AS Comment,
  Result.Score,
  Result.CreationDate,
  Result.Text
FROM @result Result, SiteToDb
WHERE
  SiteToDb.DbName = Result.DbName
ORDER BY
  Result.Score DESC,
  Result.CreationDate DESC,
  SiteToDb.SiteName,
  Result.CommentId;

fork query

edited dec 23

created dec 17 peterh - PLEASE DELETE THIS ACCOUNT