1

Please login or register to vote for this query.

(click on this box to dismiss)

Count the number of times you have a specific badge network-wide. Based on a peterh query.

Q&A for professional and enthusiast programmers

CREATE TABLE #Result (
  DbId INT NOT NULL,
  DbName VARCHAR(MAX) NOT NULL,
  UserId INT NOT NULL,
  UserName VARCHAR(MAX) NOT NULL,
  Count INT NOT NULL,
  PRIMARY KEY(DbId, UserId)
);

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,
      MIN(Users.AccountId) AS UserId,
      MIN(Users.DisplayName) AS UserName,
      COUNT(*) AS Count
    FROM
      [' + @dbName + '].dbo.Users,
      [' + @dbName + '].dbo.Badges
    WHERE
      Badges.UserId = Users.Id
      AND
      Badges.TagBased = 0
      AND
      Users.DisplayName = ''' + ##DisplayName:string## + '''
      AND
      Badges.Name = ''' + ##BadgeName:string## + '''
    GROUP BY Badges.UserId';

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

INSERT INTO #Result EXEC(@query);

SELECT DbName, UserName, #Result.Count from #Result

fork query

edited jan 17

created jan 17 James.Risner