1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

CREATE TABLE #output (
  DbName VARCHAR(MAX),
  UserId INT,
  UserName VARCHAR(MAX),
  BadgeDate DATETIME
);

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
  SELECT @query = '
    SELECT
      ''' + @DbName + ''' AS DbName,
      Users.AccountId AS UserId,
      Users.DisplayName AS UserName,
      Badges.Date AS BadgeDate
    FROM [' + @dbname + '].dbo.Badges, [' + @dbname + '].dbo.Users
    WHERE
      Badges.Name = ''' + ##BadgeName:string## + '''
      AND
      Badges.UserId = Users.Id
  ';

  INSERT INTO #output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbName;
END

CLOSE dbcursor;

SELECT
  DbName,
  UserId,
  UserName,
  BadgeDate,
  COUNT(*) OVER (PARTITION BY UserId) AS Count
FROM #output
ORDER BY Count DESC, UserId, BadgeDate, DbName

fork query

edited jan 11 19

created jan 10 19 peterh (2)