Reviews of your posts, with linked review pages, network-wide

2

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

DECLARE @output TABLE (
  DbName VARCHAR(MAX),
  PostId INT,
  Title VARCHAR(MAX),
  Score INT,
  ReviewDate DATETIME,
  ReviewType VARCHAR(MAX),
  ReviewTypeId INT,
  ReviewId INT,
  ReviewState VARCHAR(MAX),
  ReviewResult VARCHAR(MAX)
);

DECLARE @query VARCHAR(MAX);

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

DECLARE @DbName VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + @DbName + ''' AS dbName,
      ReviewTasks.PostId,
      COALESCE(Posts.Title, PostParent.Title, Tags.TagName) AS Title,
      Posts.Score,
      ReviewTasks.CreationDate AS ReviewDate,
      ReviewTaskTypes.Name AS ReviewType,
      ReviewTasks.ReviewTaskTypeId AS ReviewTypeId,
      ReviewTasks.Id AS ReviewTaskId,
      ReviewTaskStates.Name AS State,
      ReviewTaskResultTypes.Name AS ReviewTaskResult
    FROM [' + @DbName + '].dbo.ReviewTasks
    INNER JOIN [' + @DbName + '].dbo.Posts
      ON ReviewTasks.PostId = Posts.Id
    INNER JOIN [' + @DbName + '].dbo.Users
      ON Posts.OwnerUserId = Users.Id
    LEFT JOIN [' + @DbName + '].dbo.Posts PostParent
      ON Posts.ParentId = PostParent.Id
    LEFT JOIN [' + @DbName + '].dbo.ReviewTaskResults
      ON ReviewTasks.CompletedByReviewTaskId = ReviewTaskResults.Id
    LEFT JOIN [' + @DbName + '].dbo.ReviewTaskTypes
      ON ReviewTasks.ReviewTaskTypeId = ReviewTaskTypes.Id
    LEFT JOIN [' + @DbName + '].dbo.ReviewTaskStates
      ON ReviewTasks.ReviewTaskStateId = ReviewTaskStates.Id
    LEFT JOIN [' + @DbName + '].dbo.ReviewTaskResultTypes
      ON ReviewTaskResults.ReviewTaskResultTypeid = ReviewTaskResultTypes.Id
    LEFT JOIN [' + @DbName + '].dbo.ReviewRejectionReasons
      ON ReviewTaskResults.RejectionReasonId = ReviewRejectionReasons.Id
    LEFT JOIN [' + @DbName + '].dbo.Tags
      ON Posts.Id = Tags.ExcerptPostId OR Posts.Id = Tags.WikiPostId
    WHERE Users.AccountId = '
      + CAST(##SEAccountId:int?1988549## AS VARCHAR(MAX)) + ';';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @DbName;
END

CLOSE dbcursor;

--SELECT * FROM @output;

WITH Site2Db 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')
),
ReviewType2Link AS (
  SELECT 1 AS ReviewTypeId, 'suggested-edits' AS ReviewLinkPart
  UNION
  SELECT 2, 'close'
  UNION
  SELECT 3, 'low-quality-posts'
  UNION
  SELECT 4, 'first-posts'
  UNION
  SELECT 5, 'late-answers'
  UNION
  SELECT 6, 'reopen'
  UNION
  SELECT 7, 'evaluation'
  UNION
  SELECT 10, 'triage'
  UNION
  SELECT 11, 'helper'
)
SELECT
  --Site2Db.SiteName AS Site,
  --Output.DbName,
  'https://' + Site2Db.SiteName + '/questions/'
    + CAST(Output.PostId AS VARCHAR(MAX)) + '|' + Output.Title AS [Post Link],
  'https://' + Site2Db.SiteName + '/review/' + ReviewType2Link.ReviewLinkPart
    + '/' + CAST(Output.ReviewId AS VARCHAR(MAX)) AS [Review Link],
  --Output.Title,
  Output.PostId,
  Output.Score,
  Output.ReviewDate,
  Output.ReviewType,
  Output.ReviewId,
  Output.ReviewState,
  Output.ReviewResult
FROM @output AS Output
LEFT JOIN Site2Db ON Output.DbName = Site2Db.DbName
LEFT JOIN ReviewType2Link ON Output.ReviewTypeId = ReviewType2Link.ReviewTypeId
ORDER BY Output.ReviewDate, Output.DbName, Output.PostId;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results