1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

CREATE TABLE #Result (
  DbId INT NOT NULL,
  AccountId INT NOT NULL,
  Reputation INT NOT NULL,
  PRIMARY KEY (DbId, AccountId)
);

DECLARE @query VARCHAR(MAX) = '';
DECLARE @dbName VARCHAR(MAX);
DECLARE @dbId VARCHAR(MAX);

DECLARE dbCursor CURSOR FOR
SELECT dbid, name
FROM sysdatabases
WHERE
  name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
  AND (
    name NOT LIKE '%.Meta'
    OR
    name = 'StackExchange.Meta'
  );

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,
      AccountId,
      MAX(Reputation) AS Reputation
    FROM [' + @dbName + '].dbo.Users
    WHERE AccountId IS NOT NULL AND Reputation >= 101
    GROUP BY AccountId
  ';

  FETCH NEXT FROM dbCursor INTO @dbId, @dbName;
END

CLOSE dbCursor;

INSERT INTO #result EXEC(@query);

SELECT COUNT(*) FROM #result;

fork query

edited jan 11

created jan 11 peterh - PLEASE DELETE THIS ACCOUNT