Dupes and relates of your posts, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

DECLARE @output TABLE (
  DbName VARCHAR(MAX),
  LinkTypeId INT,
  DupeId INT,
  DupeUserId INT,
  DupeUserReputation INT,
  DupeUserName VARCHAR(MAX),
  DupeTitle VARCHAR(MAX),
  DupeScore INT,
  DupeDate VARCHAR(MAX),
  DupeClosed DATETIME,
  OrigId INT,
  OrigUserId INT,
  OrigUserReputation INT,
  OrigUserName VARCHAR(MAX),
  OrigTitle VARCHAR(MAX),
  OrigScore INT,
  OrigDate VARCHAR(MAX),
  OrigClosed DATETIME
);

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 = '
    DECLARE @AccountId INT;
    SELECT @AccountId = Id
    FROM Users
    WHERE AccountId = ' + CAST(##SEAccountId:int## AS VARCHAR(MAX)) + ';

    WITH Raw AS (
      SELECT Orig.Id AS OrigId, Dupe.Id AS DupeId, PostLinks.LinkTypeId
      FROM
        [' + @dbname + '].dbo.PostLinks,
        [' + @dbname + '].dbo.PostsWithDeleted Orig,
        [' + @dbname + '].dbo.PostsWithDeleted Dupe
      WHERE
        PostLinks.PostId = Dupe.Id
        AND
        PostLinks.RelatedPostId = Orig.Id
        AND
        Dupe.OwnerUserId = @AccountId

      UNION

      SELECT Orig.Id AS OrigId, Dupe.Id AS DupeId, PostLinks.LinkTypeId
      FROM
        [' + @dbname + '].dbo.PostLinks,
        [' + @dbname + '].dbo.PostsWithDeleted Orig,
        [' + @dbname + '].dbo.PostsWithDeleted Dupe
      WHERE
        PostLinks.PostId = Dupe.Id
        AND
        PostLinks.RelatedPostId = Orig.Id
        AND
        Orig.OwnerUserId = @AccountId
    )
    SELECT
      ''' + @DbName + ''' AS DbName,
      Raw.LinkTypeId,
      Raw.DupeId,
      DupeUser.Id AS DupeUserId,
      DupeUser.Reputation AS DupeUserReputation,
      DupeUser.DisplayName AS DupeUserName,
      Dupe.Title AS DupeTitle,
      Dupe.Score AS DupeScore,
      Dupe.CreationDate AS DupeDate,
      Dupe.ClosedDate AS DupeClosed,
      Raw.OrigId,
      OrigUser.Id AS OrigUserId,
      OrigUser.Reputation AS OrigUserReputation,
      OrigUser.DisplayName AS OrigUserName,
      Orig.Title AS OrigTitle,
      Orig.Score AS OrigScore,
      Orig.CreationDate AS OrigDate,
      Orig.ClosedDate AS OrigClosed
    FROM
      Raw
    LEFT JOIN [' + @dbname + '].dbo.PostsWithDeleted Orig
      ON Raw.OrigId = Orig.Id
    LEFT JOIN [' + @dbname + '].dbo.PostsWithDeleted Dupe
      ON Raw.DupeId = Dupe.Id
    LEFT JOIN [' + @dbname + '].dbo.Users OrigUser
      ON Orig.OwnerUserId = OrigUser.Id
    LEFT JOIN [' + @dbname + '].dbo.Users DupeUser
      ON Dupe.OwnerUserId = DupeUser.Id
  ';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

SELECT * FROM @output;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results