Your badges, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

CREATE TABLE #Badges (
  DbName VARCHAR(MAX),
  UserName VARCHAR(MAX),
  BadgeName VARCHAR(MAX),
  BadgeClass INT,
  BadgeDate DATETIME,
  TagBased BIT
);

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
    ';

  SELECT @query = @query + '
    SELECT
      ''' + @DbName + ''' AS DbName,
      Users.DisplayName AS UserName,
      Badges.Name AS BadgeName,
      Badges.Class AS BadgeClass,
      Badges.Date AS BadgeDate,
      Badges.TagBased AS TagBased
    FROM [' + @DbName + '].dbo.Users, [' + @DbName + '].dbo.Badges
    WHERE
      Badges.UserId = Users.Id
      AND
      Users.AccountId = ' + CAST(##AccountId:int## AS VARCHAR(MAX)) + '
  ';

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

CLOSE dbcursor;

PRINT @query;

INSERT INTO #Badges EXEC (@query);

SELECT *
FROM #Badges
ORDER BY BadgeDate DESC;

Enter Parameters

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