Favorites of a user, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #Votes (
  DbId INT,
  DbName VARCHAR(MAX),
  PostId INT,
  PostTitle VARCHAR(MAX),
  VoteId INT,
  VoteDate DATETIME
);

DECLARE @query VARCHAR(MAX) = '';

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

DECLARE @DbId INT;
DECLARE @DbName VARCHAR(MAX);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbId, @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query <> ''
    SET @query = @query + '
    UNION
    ';

  SELECT @query = @query + '
    SELECT
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      ''' + @DbName + ''' AS DbName,
      Posts.Id AS PostId,
      Posts.Title AS PostTitle,
      Votes.Id AS VoteId,
      Votes.CreationDate AS VoteDate
    FROM
      [' + @DbName + '].dbo.Votes,
      [' + @DbName + '].dbo.Users,
      [' + @DbName + '].dbo.Posts
    WHERE
      Votes.VoteTypeId = 5
      AND
      Votes.UserId = Users.Id
      AND
      Votes.PostId = Posts.Id
      AND
      Users.AccountId = ' + CAST(##AccountId:int## AS VARCHAR(MAX)) + '
    ';

  FETCH NEXT FROM dbcursor INTO @DbId, @DbName;
END

CLOSE dbcursor;

PRINT @query;

INSERT INTO #Votes EXEC (@query);

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
  Site2Db.SiteName,
  'https://' + Site2Db.SiteName + '/questions/'
    + CAST(#Votes.PostId AS VARCHAR(MAX)) + '|' + #Votes.PostTitle
    AS PostLink,
  #Votes.PostId,
  #Votes.VoteDate
FROM Site2Db, #Votes
WHERE
  #Votes.DbName = Site2Db.DbName
ORDER BY VoteDate DESC, DbId, VoteId

Enter Parameters

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