1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for apps, scripts, and development with the Stack Exchange API

CREATE TABLE #Result (
  DbId INT NOT NULL,
  DbName VARCHAR(MAX) NOT NULL,
  PostId INT NOT NULL,
  Title VARCHAR(MAX) NOT NULL,
  PostType VARCHAR(MAX) NOT NULL,
  VoteId INT NOT NULL,
  VoteType VARCHAR(MAX) NOT NULL,
  VoteDate DATETIME NOT NULL,
  PRIMARY KEY(DbId, PostId, VoteId)
);

DECLARE @query VARCHAR(MAX) = '';

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

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

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

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query <> ''
    SET @query = @query + '
    UNION ALL
    ';
    
  SELECT @query = @query + '
    SELECT
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      ''' + @dbName + ''' AS DbName,
      Posts.Id AS PostId,
      COALESCE(
        Parent.Title,
        Posts.Title,
        CAST(Posts.Id AS VARCHAR(MAX))
      ) AS Title,
      PostTypes.Name AS PostType,
      Votes.Id AS VoteId,
      VoteTypes.Name AS VoteType,
      Votes.CreationDate AS VoteDate
    FROM [' + @dbName + '].dbo.Users
    LEFT JOIN [' + @dbName + '].dbo.Posts
      ON Posts.OwnerUserId = Users.Id
    LEFT JOIN [' + @dbName + '].dbo.Posts Parent
      ON Posts.ParentId = Parent.Id
    INNER JOIN [' + @dbName + '].dbo.PostTypes
      ON Posts.PostTypeId = PostTypes.Id
    INNER JOIN [' + @dbName + '].dbo.Votes
      ON Votes.PostId = Posts.Id
    INNER JOIN [' + @dbName + '].dbo.VoteTypes
      ON VoteTypes.Id = Votes.VoteTypeId
      WHERE Users.AccountId = '
    + CAST(##SEAccountId:int## AS VARCHAR(MAX));

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

INSERT INTO #Result EXEC(@query);

SELECT *
FROM #Result
ORDER BY VoteDate DESC, DbId DESC, VoteId DESC

fork query

edited feb 2 21

created feb 2 21 peterh (2)