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 [' + @dbname + '].dbo.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;

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')
)
SELECT
  'https://' + Site2Db.SiteName AS Site,
  CASE WHEN output.LinkTypeId = 1 THEN 'Related' ELSE 'Dupe' END AS LinkType,
  'https://' + Site2Db.SiteName + '/questions/'
    + CAST(output.DupeId AS VARCHAR(MAX)) + '|' + output.DupeTitle
    AS Dupe,
  'https://' + Site2Db.SiteName + '/users/'
    + CAST(output.DupeUserId AS VARCHAR(MAX)) + '|' + output.DupeUserName
    AS DupeUser,
  output.DupeScore,
  output.DupeUserReputation AS DupeRep,
  output.DupeDate,
  output.DupeClosed,
  'https://' + Site2Db.SiteName + '/questions/'
    + CAST(output.OrigId AS VARCHAR(MAX)) + '|' + output.OrigTitle
    AS Orig,
  'https://' + Site2Db.SiteName + '/users/'
    + CAST(output.OrigUserId AS VARCHAR(MAX)) + '|' + output.OrigUserName
    AS OrigUser,
  output.OrigScore,
  output.OrigUserReputation AS OrigRep,
  output.OrigDate,
  output.OrigClosed
FROM @output AS output, Site2Db
WHERE
  output.DbName = Site2Db.DbName
ORDER BY OrigDate

Enter Parameters

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