Your badges by rarity, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

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
  IF @query <> ''
    SET @query = @query + '
    UNION ALL
    ';
    
  SELECT @query = @query + '
    SELECT
      ''' + @dbName + ''' AS DbName,
      O.*
    FROM (
        SELECT Badges.Name, Badges.Class, Badges.TagBased
        FROM [' + @dbName + '].dbo.Badges, [' + @dbName + '].dbo.Users
        WHERE
          Badges.UserId = Users.Id
          AND
          Users.AccountId = ##SEAccountId:int##
        GROUP BY Name, Class, TagBased
      ) Y,
      (
        SELECT Name, Class, TagBased, COUNT(*) AS Count
        FROM [' + @dbName + '].dbo.Badges
        GROUP BY Name, Class, TagBased
      ) O
    WHERE
      Y.Name = O.Name
      AND
      Y.Class = O.Class
      AND
      Y.TagBased = O.TagBased
  ';

  FETCH NEXT FROM dbcursor INTO @DbName;
END

EXEC(@query);

Enter Parameters

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